Trouble getting a simple grand total of a Measure

[Anonymous]

Hello Power BI experts

I am trying to retreive the SUM of the column highlighted in red that should equate to 52.36.

It's easily done in something like Excel.

The issue I am facing is that you can only use the DAX function SUM on a real coloumn and not on a measure like Productivity. My Productivity measure is :

Productivity = iferror ( SUM([Quantity]) /  SUM(vSubmissionFull[AllocatedTime]) * 420 , blank() )

Using SUMX instead only seemed to bypass all the filters so also tried ALLSELECTED with no avail. Thanks in advance !

[Anonymous]

@Anonymous,

Create a measure using the DAX below and drag it to Matrix visual, then check if it returns your expected result.

Measure = IF(COUNTROWS(VALUES(vSubmissionFull[DateColumn]))=1, [Productivity],SUMX(VALUES(vSubmissionFull[DateColumn]),[Productivity]))

Regards,
Lydia

[Anonymous]

Hi Lydia

My Prodcutivity Totay is still being split by Week Name (Text - Reason I used Text was the graph didnt auto format to months / years )

Productivity Total = IF(COUNTROWS(VALUES(vSubmissionFull[Week Name]))=1, [Productivity],SUMX(VALUES(vSubmissionFull[Week Name]),[Productivity]))

[Anonymous]

@Anonymous,

Create another measure using DAX below.

Measure  = CALCULATE([Productivity Total], ALL(vSubmissionFull))



Regards,

[Anonymous]

Hi Lynda

I tried what you mentioned and if it helps, ive attached the raw data used for this, if it helps !! thanks so far.

prod Grand Total= CALCULATE([Productivity Total], ALL(vSubmissionFull))

https://www.dropbox.com/s/8p4jhod7fmuvwdt/Datafile.xls?dl=0

[Anonymous · Accepted solution]

Hi @Anonymous 

I got it myself in the end, but with your help.

You were close with the combination of the SUMX and VALUES function. The ALLSELECTED provided the filtered subset of vSubmissionFull that was being imposed on the matrix to get the correct result of 52.36, shown in Grey.

Answer is:

Productivity Total = CALCULATE(SUMX(VALUES(vSubmissionFull[Week Name]),[Productivity]),ALLSELECTED(vSubmissionFull))

[user18325239]

You are amazing! I spent hours on this

[Greg_Deckler]

Check out this design pattern:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

It deals with a similar issue of summarizing and doing aggregations on measures.

[Anonymous]

I forgot to mention that I am using DirectQuery with my SSAS and Powerbi on-premis report server so I have some restrictions.

WIll check out that forum

thanks by the way