cancel
Showing results for
Did you mean:
Helper I

## Trouble getting a simple grand total of a Measure

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
Helper I

## Re: Trouble getting a simple grand total of a Measure

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.

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

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

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

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

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

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

Helper I

## Re: Trouble getting a simple grand total of a Measure

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.

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

Announcements

#### 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!

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

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.

Top Solution Authors
Top Kudoed Authors