Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Keegan_Patton
Advocate I
Advocate I

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

Keegan_Patton_1-1670613097013.png

 

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

 

 

Keegan_Patton_0-1670613049576.png

 

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?

 

Keegan_Patton_2-1670613173845.png

 

Thank you for the help!

1 ACCEPTED SOLUTION

Hi @Keegan_Patton 

 

I tried to replicate your case as best as possible,

Mikelytics_0-1670624889310.png

BAse sample -> your activity ...

Mikelytics_1-1670624902554.png

Warranty for the distinct count

Mikelytics_2-1670624925083.png

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

Mikelytics_3-1670625254527.png

 

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

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

LinkedIn

 

 

 

 

 

 

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

View solution in original post

14 REPLIES 14
Mikelytics
Resident Rockstar
Resident Rockstar

Hi  @Keegan_Patton 

 

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.

Keegan_Patton_0-1670619167148.png

 

 

HI @Keegan_Patton ,

 

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.

Hi @Keegan_Patton ,

 

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 :

Mikelytics_0-1670621619280.png

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

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

LinkedIn

 

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

Keegan_Patton_1-1670622580010.pngKeegan_Patton_2-1670622586031.png

Thank you so much for your continued assistance!



Hi @Keegan_Patton 

 

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

can you please try the following 

start from the following measure which you had

Mikelytics_1-1670622887367.png

 

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.

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

LinkedIn

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

Hi @Keegan_Patton 

 

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 .

Mikelytics_2-1670623934083.png

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.

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

LinkedIn

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

Hi @Keegan_Patton 

 

I tried to replicate your case as best as possible,

Mikelytics_0-1670624889310.png

BAse sample -> your activity ...

Mikelytics_1-1670624902554.png

Warranty for the distinct count

Mikelytics_2-1670624925083.png

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

Mikelytics_3-1670625254527.png

 

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

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

LinkedIn

 

 

 

 

 

 

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

Greg_Deckler
Super User
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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg!

Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Keegan_Patton ,

 

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

 

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

Mikelytics_0-1670614513733.png

 

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

Mikelytics_1-1670614562172.png

Mikelytics_2-1670614579774.png

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

Mikelytics_3-1670614619007.png

 

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.

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

LinkedIn

 

 

 

 

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

Keegan_Patton_0-1670616334136.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors