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

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

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
v-yuezhe-msft
Microsoft
Microsoft

@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.

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

@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.

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

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

Greg_Deckler
Super User IV
Super User IV

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.


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

@ me in replies or I'll lose your thread!!!

I have a 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 Super User!




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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors