DAX - calculate % of Grand Total using COUNTROWS

Hi all,


Trying to get a percentage of the grand total for a couple of times.

Service Names	Overdue	On Time	Total	% on time	% of Grand Total
Service 1	21	45	66	68%
Service 2	0	14	14	100%
Service 3	3	8	11	73%
Service 4	112	314	426	74%
Grand Total	136	381	517	74%

I would like Total / Grand Total.
Service 1 = 66 / 417
Service 2 = 14 / 417
Service 3 = 11 / 417
Service 4 = 426 / 417
Grand Total = 417/417

Can this be done?

The data in my pivot table has using [Measures]. I cannot seem to get the correct % / formula.

Can I ask a question or two ... you are asking about DAX formula for this, but seem to want % of Grand Total in a PivotTable? Is this for a PowerPivot table, or a PivotTable? In a PivotTable % of Grand Total is simple (Value Field settings, Show Values As ... % of Grand Total).

Originally Posted by GlennUK

Can I ask a question or two ... you are asking about DAX formula for this, but seem to want % of Grand Total in a PivotTable? Is this for a PowerPivot table, or a PivotTable? In a PivotTable % of Grand Total is simple (Value Field settings, Show Values As ... % of Grand Total).

Thanks GlennUK for replying.
This is in PowerPivot. Apologies, I thought by mentioning Dax it was implied.

I am still struggling.

Isn't it just =[Total]/SUM([Total])

Sadly GlennUK the SUM function in DAX can only be used to reference a Column Name.
Since my Measures are COUNTROWS I was hoping there would be a function to grab the Total for Service 1 / Grand Total of the Total Column ( 66 / 517 ).

The Overdue Column : Overdue:=CALCULATE ( COUNTROWS ( Visits ), FILTER ( Visits, Visits[Case] = "LAA"), FILTER( Visits, Visits [TIME] = "Overdue" ))
The On Time Column : OnTime:=CALCULATE ( COUNTRROWS (Visits ), FILTER( Visits, Visits[Case] = "LAA"), FILTER(Visits, Visits [TIME] = "on time" ))
Total Column : Total:=CALCULATE ( COUNTROWS ( Visits ), Visits [Case] = "LAA" )


I think the only way to do this is to create a MEASURE for each Service but that's not ideal.
Alternatively, turn the Pivot Table into OLAP and use CUBE functions to gather information.

If there is a way, its something I would prefer.

DAX functions respect data hierarchies ... look at this data and the PivotTable via Data Model using SUM in a DAX calculated column:

I guess you don't see what I mean ... this is the PowerPivot view:

Hi GlennUK,

Thank you for that and the examples you provided.
Also, apologies for the late response.

In terms of simplicity, that is exactly how I would like PowerPivot to look.

However, the type of data set I have does not allow me to have a Total Time column within my data set.
The Overdue and On Time are within a single column.
There is also another column which holds Key ID's which I need to report on "LAA" / "NIC" / "PC" but there are more Key ID's within this column that I do not care for.

I would need a formula which would say if there is an "LAA" and it is "Overtime" put a 1
Which would then act as a form of a total.
The problem being what about the "NIC"'s and "PC"'s.

Would that then need to be put into three separate columns with a total column suming them up?
If so, what kind of formula would be best? Or have I over complicated things?

Thank you for your help.