cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jude_moore Helper I
Helper I

Trouble getting a simple grand total of a Measure

Prod_chart.png

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
jude_moore Helper I
Helper I

Re: Trouble getting a simple grand total of a Measure

Hi @v-yuezhe-msft 

 

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))

 

Prod_chart5.png

 

 

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

Re: Trouble getting a simple grand total of a Measure

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-yuezhe-msft
Moderator

Re: Trouble getting a simple grand total of a Measure

@jude_moore,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jude_moore Helper I
Helper I

Re: Trouble getting a simple grand total of a Measure

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

jude_moore Helper I
Helper I

Re: Trouble getting a simple grand total of a Measure

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]))

 

Prod_chart2.png

Moderator v-yuezhe-msft
Moderator

Re: Trouble getting a simple grand total of a Measure

@jude_moore,

Create another measure using DAX below.

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


Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jude_moore Helper I
Helper I

Re: Trouble getting a simple grand total of a Measure

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

 

Prod_chart4.png

jude_moore Helper I
Helper I

Re: Trouble getting a simple grand total of a Measure

Hi @v-yuezhe-msft 

 

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))

 

Prod_chart5.png

 

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors