Measure Totals, The Final Word 11-06-2023 22:24 PM

[Greg_Deckler]

With apologies to Theodor Geisel...

Measure totals have you perturbed?
Fear not!
It's Measure Totals, The Final Word,

These measures work with matrices,
They work with tables,
They work with rows and columns and labels.

They work in the daytime,
They work at night,
They work to make sure the totals are right!

Now that you've seen them,
Now that you've heard,
Shout it out loud, it's Measure Totals, The Final Word!

At some point, we've all been frustrated by measure totals. If you want to understand why, read this post.

The technique employed here is fairly simple and should work in all "standard" cases of where you just want the Total line to, well, display the total (sum) of a measure. For more complex scenarios, see my Matrix Measure Total Triple Threat Rock & Roll measure.

Essentially, create a measure, any measure, that performs your desired calculation and returns the correct result at the row level. This becomes your "m_Single" measure. Now, create an "m_Total" measure that performs a SUMMARIZE of your data, exactly as how it is displayed in your table or matrix and use the "m_Single" measure within that SUMMARIZE statement to provide the values for the individually summarized rows. Finally, perform a SUMX across that summarized table. The measures presented in this PBIX file also do a HASONEVALUE check that isn't really necessary in most cases but perhaps lends a little confidence to the user that the SUMX is only employed in the Total line and might also add some performance improvements.

In effect, you are recreating the displayed visualization in memory as a table and then doing a summation across that table for the total line, as you would intuitively expect a total line in a table or matrix to work.

So, if we have a measure like:

m_Single = SUM(Table1[Value])-50

This measure will cause problems in total lines. So, if we are summarizing by [Name], we create this measure:

m_Total 1 = 
VAR __table = SUMMARIZE('Table1',[Name],"__value",[m_Single])
RETURN
IF(HASONEVALUE(Table1[Name]),[m_Single],SUMX(__table,[__value]))

If we are summarizing by [Category1], we create this measure:

m_Total 2 = 
VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single])
RETURN
IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

And so on...

We use these "m_Total" measures in our visualizations. The "m_Single" measure is still used, but not directly in the visuals themselves.

Is it annoying to have to create multiple measures and specifically tailor them to each individual visual? Yes, yes it is.

eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[pbinewbie_22]

Hey, I've tried this method and it worked for two columns. The totals for Joined Adjust and Offered Adjust are now correct.

However, if you sum up the rows in the Offer Adjust TOTAL, it's not adding up the values "3" and "2" (highlighted in yellow) in the total. The total should be 41, not 36.

I used the same formula as the ones used in the Joined Adjust Total and Offered Adjust column.

[wchristiang]

HI, 

I have the following example of a situation in a Power BI matrix:

	MONTH 1	MONTH 2	TOTAL
VALUES	5	2	2,85

See that the result of my DAX formula in month 1 is 5 and in month 2 is 2.

So the sum should be 7 (TOTAL), but is bringing 2.85.

This is because my DAX formula has sum, multiplication and division. Because of this when I join the 2 months together,  bring me a result with total considering the DAX formula acumulating the 2 months. 

For Example:

Month 1

(3 + 7) / 2 = 5

Month 2

(6 + 4) / 5 = 2

Month 1 and month 2 together the total is considered the sums of dividends by the sum of the divisors of the two months together to bring 2.85 as a result as follows:

(3 + 7 + 6 + 4) / (2 + 5) = 2,85

Is there any way for the total column that is already automatic from Power BI as shown below, to sum the columns and bring the value 7 instead of 2.85?

The real matrix is like that, where the sum of "Valor Realizado of January" + "Valor Realizado of February" = 9.276,23.

[Rai_BI]

I don´t know how to apply this to my issue, please can someone help me?
Re: Adjust the measure to also calculate the sum f... - Microsoft Fabric Community

[Gangasaniravi]

@Greg_Deckler  what if we use stand alone table as field parameter in palce of Category in visual , i tried with field parameter in measure it is giving composite key error

[JSIQUEI-YYC-ENB]

HI @Greg_Deckler nice post!

One question, in case if I am using a Matriz table with level 1 being 'Name' and level 2 being "Category1' , as mentioned in your example, how can I have a measure to calculate the correct values in both levels? the m_Total 1 measure will give the right infomration for the 'Name' level, but if I drill down to Category1, it will mess up again... it;s really anoing...thanks! 

[mbacicci]

Hi, I've noticed that all my measures are doing the same with totals.
I've also tried to rewrite them as additive but it doesn't fix the issue. Could you help me with these?
Thank you!

New Customers Year =

VAR currentCustomers = VALUES('_Combined'[Customer name])

VAR currentDate = MAX('*DATE TABLE'[Date])

VAR prevYear = YEAR(currentDate) - 1

VAR pastCustomers =

    CALCULATETABLE(

        VALUES('_Combined'[Customer name]),

        ALL('_Combined'),

        YEAR('*DATE TABLE'[Date]) = prevYear,

        '*DATE TABLE'[Date] < DATE(YEAR(currentDate), 1, 1)

    )

VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)

RETURN

    COUNTROWS(newCustomers)

-----

New Customers Sales =

VAR currentCustomers = VALUES('_Combined'[Customer name])

VAR currentDate = MAX('*DATE TABLE'[Date])

VAR pastCustomers = CALCULATETABLE(

    VALUES('_Combined'[Customer name]),

    ALL('_Combined'),

    YEAR('*DATE TABLE'[Date]) < YEAR(currentDate)

)

VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)

RETURN

    CALCULATE(

        SUM('_Combined'[Amount EUR]),

        FILTER(

            '_Combined',

            '_Combined'[Customer name] IN newCustomers

        )

    )

-----

MAT =

VAR StartDate2018 = DATE(2018, 1, 1)

VAR StartDate2019 = DATE(2019, 1, 1)

VAR CurrentDate = MAX('*DATE TABLE'[Date])

RETURN

IF(

    CurrentDate >= StartDate2018 && CurrentDate < StartDate2019,

    CALCULATE(

        [Total Sales],

        DATESBETWEEN(

            '*DATE TABLE'[Date],

            StartDate2018,

            CurrentDate

        )

    ),

    IF(

        CurrentDate >= StartDate2019,

        CALCULATE(

            [Total Sales],

            DATESINPERIOD(

                '*DATE TABLE'[Date],

                MAX(StartDate2019, CurrentDate),

                -12,

                MONTH

            )

        )

    )

)

[Greg_Deckler]

@mbacicci So, it depends on how you are using these in a visual. If those are your base measures and they work at the row level, then for the first one for example, if you are summarizing this measure in table based on year it would look something like:

New Customers Year TOTAL = 
  VAR __Table = SUMMARIZE( '*DATE TABLE', [Year], "__NewCustomers", [New Customers Year] )
  VAR __Result = IF( HASONEVALUE( '*DATE TABLE'[Year] ), [New Customers Year], SUMX( __Table, [__NewCustomers])
RETURN
  __Result

But, if you are summarizing in your table/matrix visual by year and month, the solution will be slightly different. So, really need the details to provide a specific solution. So, in this case, you would just use the new "TOTAL" version of the measure since it would work in both row and total lines and you could hide the other measure.

[hamedelbhrawy]

its retrun wrong rwo Total how to solve  ? 

TotalSales =

VAR Sales2022H1 = SUM('RecieptDetials 2022 H1'[Net amount])

VAR Sales2022H2 = SUM('RecieptDetials 2022 H2'[Net amount])

VAR Sales2023H1 = SUM('RecieptDetials 2023 H1'[Net amount])

VAR Sales2023H2 = SUM('RecieptDetials 2023 H2'[Net amount])



RETURN

CALCULATE(

    Sales2022H1 + Sales2022H2 + Sales2023H1 + Sales2023H2

)

[scondon1985]

Hi @Greg_Deckler , thanks for all your postings! I tried the below in the attached project (page 2).

m_Total 2 = 
VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single])
RETURN
IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

My table is DailyBalance, grouping by Date, summing the value LatestValue. Shouldn't my "Test" column be showing the total of LatestValue by day instead of the same value as LatestValue?  

[stitch_es]

This looks like something I could use - but I'm a newbie Power BI analyst.  If I have a matrix with multiple levels, how would I apply this to the subtotals?  See screenshot  I have a measure that determines if the lineitem should be displaying a max or a sum - but the subtotal is totaling everything, not just the max it displays

[terryh1]

Hi, the issue of totals when using functions other than sum has been around for a while , and is basically due to the different views on what to do with the total in such instances:

should it be:

1.a max of all the single lines ie returns a value you will already have in on of your rows. (the default behaviour)

2. a sum of all the previous max values shown in rows (i think the desired behaviour a lot want to see)

The simplist way in a simple two tier matrix as shown to deal with it is the use of the Sumx function with the Max being done at each row level., the table referred to is the fields in the matrix. A very good explanation of how to do this is https://youtu.be/yw0QHu9V4UQ?si=YqMmQV7zMNvvjH1-  

You can also use the inscope function to drive a switch function , where you basically test if you are on a total line ,see this vid https://youtu.be/1tdufLz7_0U?si=9Amxsr2x4zCSAAv5

but yes not as easy as it should be..

Enjoy

[Greg_Deckler]

@stitch_es Well it definitely depends on the circumstance but the general solution is to use SUMMARIZE and as the groupings, include the Rows in your matrix but the grouping will be different depending on where you are in your hierarchy. This additional article should help with that:

Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community

[stitch_es]

Thank you for responding so quickly @Greg .  Maybe you can direct me in where I'm going wrong?  I have a measure that determines if the row should show a sum of line items or a max- that's working (max vs sum), and then i created a measure using your suggestion here for the totals to take that max and use that to sum it up - but it doesn't seem to be working, it just sums up as if the max is not part of the equation.  See screenshots for explanation.

[Greg_Deckler]

@stitch_es Hard to know for sure without sample data to play with but it seems like you are not summarizing correctly. I would think you would need at least 2 groupings, ParentCategory and LineItemName.

[AmitWaware]

Hi @Greg_Deckler ,

Can you please help me understand why Totals are wrong in a Matrix below .

I am attaching .pbix file also

In above matrix i am spreading value of each NewVal  column till the date of Next NewValue,I got the expected spread results 

But ......Totals are Wrong.....

Below is The measure 

Spread Value =

 Var val=    CALCULATE(count(Data[NewValue]),ALLEXCEPT(DimDate,DimDate[Date]))
                    
 var MinDate =CALCULATE(MIN(Data[CreatedDate]),
                        ALLEXCEPT(Data,Data[ProjectName],Data[Index]))
var MaxDate =CALCULATE(MAX(Data[EndDateFinal]),
                        ALLEXCEPT(Data,Data[ProjectName],Data[Index]))
return  
                 IF(HASONEVALUE(Data[CreatedDate]),COUNTX(KEEPFILTERS(Data),val),
           IF(HASONEVALUE(DimDate[Date])=FALSE() && max(DimDate[Date])>MaxDate,BLANK(),

          IF(HASONEVALUE(DimDate[Date])=FALSE() && MIN(DimDate[Date])>MinDate,Val

                )))

[canddig]

Hi @Greg_Deckler ,

This is awesome and I think would solve my problem that I am having but I am new to writing dax and the forumla I am trying to configure is already very.... busy. Could you take a look and help guide me toward a solution that would work? 

RVU % = IF(and(sum(Summary[25th % tile PTD])=0,sum(Summary[wRVU PTD])=0),0,IF(sum(Summary[25th % tile PTD])=0,0,IF(sum(Summary[wRVU PTD])<=sum(Summary[25th % tile PTD]),sum(Summary[wRVU PTD])/sum(Summary[25th % tile PTD])*0.25,IF(sum(Summary[wRVU PTD])<=sum(Summary[50th % tile PTD]),((sum(Summary[wRVU PTD])-sum(Summary[50th % tile PTD]))/(sum(Summary[50th % tile PTD])-sum(Summary[25th % tile PTD]))*0.25)+0.25,IF(sum(Summary[wRVU PTD])<=sum(Summary[75th % tile PTD]),((sum(Summary[wRVU PTD])-sum(Summary[50th % tile PTD]))/(sum(Summary[75th % tile PTD])-sum(Summary[50th % tile PTD]))*0.25)+0.5,IF(((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75>0.99,(((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75),((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75))))))

It calculates correctly on the row level but same issue as you stated the totals are very messed up and they are meant to be a percentage. Any guidance, insight or thoughts? 

Thanks so much in advance, 

Candace 

[Power_Chum]

@Greg_Deckler I feel like Salieri reading Mozart's sheet music. I regret that I have but one kudos to give for my... Deckler? This is some powerful dark magic you got here. Thanks for sharing.

[Anonymous]

Hi @Greg_Deckler, 

I have this table: 

In the second column i did the MAX function and I was trying to get the sum of that column

I tried what you said:

m_Total =

VAR __table = SUMMARIZE('Documents',[No-QR7],"__value",[max nb pages])

RETURN

IF(HASONEVALUE(Documents[No-QR7]),[max nb pages],SUMX(__table,[__value]))

but it doesn't give the correct sum, it supposed to be 1034 but it gives 964 (it looks like it stops adding after the 4 9s.)

Can you help me please?

[Greg_Deckler]

@Anonymous My guess is that it is perhaps removing duplicates. For example, I see in your image that you have 13-7965/7966/7967 listed twice so do you want that counted twice? If you use SUMMARIZE, that returns distinct values and then if you use MAX you would only get one 6, not two. Otherwise, you would want to use CONCATENATEX on your __table variable in your return statement to get a list of what all is in that table so that you can see what is going on.

[Jdpatino21]

Hello! @Greg_Deckler  I have a slighty different issue I have the following:
I´m trying to recreate a forecast based on YTD actual values and budget to complete, so I have a couple of tables:

Fact tables:

Presupuesto "Budget"

contabilidad Balance_Prueba "actual"

Ajustes Forecast "forecast Adjusments"

'Ajustes EEFF' "Financial Adjustments"

So, what I´m tryign to do is, that if there is not an "actual" well get the forecast.... on this "m_single" measure

And then I did the "m:total" version

However, when I try them on a table..... it get this result

Nor the single o the total  are summarizing correctly.

can you help me?