cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Producing Correct Totals and Subtotals with a SUMX

Hello all, I have a common issue that I cannot seem to find a solution for. I have a matrix, that has two dimension fields for the rows vehicle make and vehicle year.

the values are a measure that counts the number of unique vehicles.  as seen below

For my total and subtotal measure I have used a common combination of SUMX( with a nested SUMMARIZE.

I have come to a correct Grand Total and Subtotals for the first subcategory, but I cannot seem to find a way to generate subtotals for my second category, I have tried the inclusion of ALLEXCEPT, to no avail.

Has anyone determined a solution for correcting totals and subtotals based on multiple criteria in a matrix?

Thank you for the help!

1 ACCEPTED SOLUTION
Resident Rockstar

I tried to replicate your case as best as possible,

BAse sample -> your activity ...

Warranty for the distinct count

base divide measure:

``````SUP | DistinctCountWithDivide =
DIVIDE(
DISTINCTCOUNT('Base Sample'[Values]),
5
)``````

initial SUMX measure

``````SUMX | SumxOver2Dimensions =

SUMX(
SUMMARIZE(
'Base Sample',
'Base Sample'[Type],
'Base Sample'[Category]
),
CALCULATE([SUP | DistinctCountWithDivide])
)``````

DISTINCT measure for the warranty filter

``DistinctCount Warranty = DISTINCTCOUNT(Warranty[Category])``

new SUMX measure combining everything:

``````SUMX | SumxOver2DimensionsWithFilter =
SUMX(
Dim_Category,
IF([DistinctCount Warranty]>0,
SUMX(
GROUPBY(
'Base Sample',
'Base Sample'[Type],
'Base Sample'[Category]
),
CALCULATE([SUP | DistinctCountWithDivide])
)
)
)``````

results (all 4 measures):

as you can see the 4th measure is now a filtered version of the 2nd measure based on the warranty count

@Keegan_Patton when I posted this there wert missing a few picture so I did an update of the post

There is some complexity in your case but its really hard to help better from remote ^^

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

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

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
14 REPLIES 14
Resident Rockstar

to be honest I do not get the context between your challenge and the picture you provided which is a distinct function. Can you maybe show on the actual report with data hidden what the problem is?

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Thank you for your continued help @Mikelytics , Here is a picture that may do a better job showing my current scenario. My table has two rows, dimension 1(make) and dimension 2 (vehicle year), the values are show in the [Original Measure] and my attempts with [TEST MEASURE1]. There is an active filter on this matrix in which the number produced by [Filter Measure] is > 0.

Your solution previously provided does indeed work when there is no outside filter context involved, You can see in the image of the matrix that when the visual is filtered by [Filter Measure], the subtotals for year are duplicated.

Resident Rockstar

Thank you for the further information and I think I understand what mean even I am not sure. 😄

If I understand correctly then you use the measure [All UNits Daily Average] is the [Orginal Measure], right? Can you please try to explain difference between equid and wc_uid as well as why you do a DISTINCTCOUNT on wc_UID. Is the DISTINCTCOUNT to filter single rows?

Best regards

Michael

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

I apologize for the ambiguity, I am a bit new at conveying issues here, Yes [All Units Daily Average] is [Original Measure], equid is a distinct dimension for a single vehicle, wc_uid is related to a fact table that checks whether or not there is an existing order on the vehicle.

The purpose for the distinct count is to only return vehicles that have an order, but I need to also have a column that shows the totals and subtotals without that filter applied.

Resident Rockstar

No problem and thank you! 🙂

So in genereal I think one problem is why it crashes only on the lower level and not above because you put only the make group ind the ALLEXCEPT and not the vehivle year

so can you please try to also put the vehicle year into the allexcept liek :

....make_group],Dim_Equipment[vehicle year])

if this does not work can you please show how the Dim Equipment is related to Fact Warranty and to Fact Active... ?

is it 1:n and is filter direction one directional or bi directional?

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

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

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hello @Mikelytics ! So I have tried the inclusion of  year into the ALLEXCEPT() statement but it unfortunately breaks the totals completely, below if the result when I alter [Test Measure] to include that. Also below is a snip of the relevant aspects of my model.

Thank you so much for your continued assistance!

Resident Rockstar

highly appreciate your feedback and your case is quite fun! 🙂

can you please try the following

start from the following measure which you had

the please replace the yellow text by

FILTER( Dim_Equipment, DISTINCTCOUNT( Fact Warranty ...[wc_uid] )>0 ),

If you do that you have to take out the filter measure.

meanwhile I try to replicate your case in my model

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

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

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

I took another look on your measure and thee main problem is that you only iterate through the make group and not as initially suggested by by through make group and year .

did you try to put the year in both positions ->> in the Summarize AND in the all except? what happens then?

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

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

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

I tried to replicate your case as best as possible,

BAse sample -> your activity ...

Warranty for the distinct count

base divide measure:

``````SUP | DistinctCountWithDivide =
DIVIDE(
DISTINCTCOUNT('Base Sample'[Values]),
5
)``````

initial SUMX measure

``````SUMX | SumxOver2Dimensions =

SUMX(
SUMMARIZE(
'Base Sample',
'Base Sample'[Type],
'Base Sample'[Category]
),
CALCULATE([SUP | DistinctCountWithDivide])
)``````

DISTINCT measure for the warranty filter

``DistinctCount Warranty = DISTINCTCOUNT(Warranty[Category])``

new SUMX measure combining everything:

``````SUMX | SumxOver2DimensionsWithFilter =
SUMX(
Dim_Category,
IF([DistinctCount Warranty]>0,
SUMX(
GROUPBY(
'Base Sample',
'Base Sample'[Type],
'Base Sample'[Category]
),
CALCULATE([SUP | DistinctCountWithDivide])
)
)
)``````

results (all 4 measures):

as you can see the 4th measure is now a filtered version of the 2nd measure based on the warranty count

@Keegan_Patton when I posted this there wert missing a few picture so I did an update of the post

There is some complexity in your case but its really hard to help better from remote ^^

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

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

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hello @Mikelytics , Thank you for all of your help, while this is not the exact solution for my scenerio specifically, this is certainly a solution for this issue more generally, thanks again!

Super User

@Keegan_Patton First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg!

Resident Rockstar

I tried to replicate your request and please try the approach below:

1) my dummy data with 2 dimensions and dummy values:

2) I created a DIVIDE FUnction by taking distinct count of value column and dividing it by 5

3) Now I added the following SUMX function to make senseful totals an all levels

I am not sure why you use all that ALLEXCEPT (maybe other filters you want to ignore or something?).

So the formula template would look like this:

``````SumxWith2Attributes =
SUMX(
SUMMARIZE(
YourTable,
YourTable[Dimension1],
YourTable[Dimension2]
),
CALCULATE([YourBaseMeasure])
)``````

Best regards

Michael

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

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

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Thank you for the quick response, that is definitely helpful, The reason I went down the path of using ALLEXCEPT is that I have another measure acting as a filter on this matrix that is affecting the totals, so I am trying to cut that filter context out. That is where it seems to be getting tricky, any suggestions in that scenario?  The measure being used as a filter is  this

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors