Producing Correct Totals and Subtotals with a SUMX

[Keegan_Patton]

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!

[Mikelytics]

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?

[Keegan_Patton]

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.

[Mikelytics]

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

[Keegan_Patton]

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.

[Mikelytics]

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 :

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

[Keegan_Patton]

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!

[Mikelytics]

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

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

[Mikelytics]

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 .

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

[Mikelytics · Accepted solution]

Hi @Keegan_Patton 

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.

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

LinkedIn

[Keegan_Patton]

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]

@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

[Keegan_Patton]

Thank you Greg!

[Mikelytics]

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:

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.

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

LinkedIn

[Keegan_Patton]

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