Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Greg_Deckler
Super User
Super User

Dealing with Measure Totals

This one has come up quite a bit recently. The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, the total row is correct for the measure, it's just not what most people expect. What people expect is for the "Total" to display the sum of the values in the column. Measures do not do this. Measures respect the context of the Total row and is calculated within that context. Therefore, a Measure used in a column in a table visualization will likely have an unexpected value in the Total column.

 

There are a couple ways of fixing this. The easiest is to turn off the Total row.

 

Assuming that is not what you want, you can use the HASONEFILTER function to get around this issue. However, the ultimate solution will depend on how your measure is calculated.

 

For example, given the following data:

 

Year Amount

Year1500
Year21500
Year32000
Year4100
Year5800

 

We wish to find the total extra Amount spent above 1000 for each year. If the amount is not over 1000, we wish to display 0. To this end, we create a measure:

 

MyMeasure = IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000) 

Adding this to a Table visualization along with Year, we get the correct answer for each of the rows, but the Total line displays 3900, not 1500 as we would expect. The figure 3900 is calculated because the Measure is performing its calculation for ALL of the rows in the table, so the calculation is (500 + 1500 + 2000 + 100 + 800) - 1000 = 3900.

 

Correct, but not what was expected.

 

To get around this problem, use HASONEFILTER to calculate the Measure one way within a row context and another way within the Total row context, such as:

 

MyMeasure2 = IF(HASONEFILTER(Table[Year]),
IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000),
SUMX(FILTER(Table,[Amount]>1000),[Amount]-1000)
)

Breaking this down, we essentially wrap our original measure in an IF statement that has the HASONEFILTER function as the logical test. If HASONEFILTER equals true, we calculate our Measure as before. However, if HASONEFILTER is false, we know that we have a Total row and we calculate our Measure a different way.

 

 

 

 

 

 

 

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
104 REPLIES 104
Anonymous
Not applicable

Hi, My problem statement is a little different. Below is my table.

Name            forecast   capacity  Leaves               Availability                            Answer column

Abc                   33             37          0          if(37-33-0 < 0, 0, 37-33-0)                       4

efg                    42             37          8          if(37-42-8 < 0, 0, 37-42-8)                       0

Total to get                                                                                                                  4

Hi,

Do these measures work?

Availability inter = if([capacity]-[forecast]-[leaves]<0,0,[capacity]-[forecast]-[leaves])

Availability = SUMX(VALUES(Data[Name]),[Availabiliy inter])

Drag the Availability measure to your visual.  My assuption is that capacity, forecast and leaves are measures that you have already written.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bob_by
New Member

Hi,

 

I'm strugging with the issue where I need to calculate one measure in a two ways based on the values.

 

The data model is much more complex, however to simplify it let's imagine that I have one column and two measures.If value in column is "D" pick value from second measure, otherwise first measure.

When I use below formula the row by row calulation is good however total is wrong. 

 

Measureif = IF(SELECTEDVALUE('Table'[Column1])="D",[Measure2],[Measure1] )

 

I need to present all values in table and I cannot make columns instead of measures. I understand what SELECTEDVALUE is doing however don't know what other formula should I use n that case. 

 

All suggestions are more than welcomed.

 

Thank you

 

image_2021-05-05_201216.png

 

The result of the Measureif measure is correct.  What problem are you facing? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

the total value is wrong.

bobby

Hi,

Try this measure

=SUMX(VALUES(Table[Column1]),[Measureif])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Despues de dias de buscar este resultado, di con el usuario correcto, que alivio; Gracias Ashish_Mathur, como dirian en mi Pais, "MAQUINA"

 

@Ashish_Mathur you saved my day 😀

Thank you

You are welcome.  If my previous reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi All. 

I need your support about this detail. My Totals didnt match. 

The real total is 439K (Correct), but the total power bi gives $610. 

miiclaudia_0-1625016519705.png

 

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Everyone, 

 

Im not sure if this is the right subject to be asking this but I really need some help. I'm trying to calculate the sum of the column "Cases Needed". The result shoul be 9 instead of 53. I Understand why this difference occurs but I don't know how to fix it in my case. This table almost all the information are measures that do not refer to columns. If there is any way that someone can help me, I appreciate it. 

 

AMIPowerBi_0-1617123610150.png

Cases Needed = ROUNDUP(DIVIDE(IF([ETD]>[Weeks],VALUE(0),([Weeks]-[ETD])*[Average]),[Case]),0)

 

NewCasesNeeded =
IF(
HASONEFILTER(OBJ_TAB[UPC]),
ROUNDUP(DIVIDE(IF([ETD]>[Weeks],VALUE(0),([Weeks]-[ETD])*[Average]),[Case]),0),???? (No idea how to finish the measure)
 
 

Hi,

Does this measure work?

Measure1 = SUMX(VALUES(OBJ_TAB[UPC]),[Cases Needed])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks!!! Is working 🙂 @Ashish_Mathur 

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi, 

I'm also experiencing total value issues.. I'm using this function in a measure:

 

BonusTest = 
VAR BonusMin = SELECTEDVALUE('BonusSpecs'[BonusMin])
VAR BonusMax = SELECTEDVALUE('BonusSpecs'[BonusMax])
VAR BonusValueMin = SELECTEDVALUE('BonusSpecs'[BonusValueMin])
VAR BonusValueMax = SELECTEDVALUE('BonusSpecs'[BonusValueMax])
RETURN
SWITCH(
TRUE()
,SUM(Income[Value]) < BonusMin, 0
,SUM(Income[Value]) < BonusMax, BonusValueMin
,BonusValueMax
)

 

 

And as you can see, the total value is incorrect:

shockl1ne_0-1611224881251.png

Although I'm not sure on how to use HASONFILTER in my case, any tips?

 

Alex_R
New Member

Hi all
I spent days to solve my measure totals issue and the solution from @Greg_Deckler  seems to be the closest one to fit my expectations.
However, I don't quite get how to use the HASONEFILTER function in my case correctly and hope you could help.

I have got a table visualization, where only Sales is a table column, while Sales Prev, Sessions and Sessions Prev are measures.

CountrySalesSales PrevSessionsSessions Prev
uk1000500100008000
us200010001500012000
de3000   
fr20005001200010000
Total800020004900040000

Sales Prev calculates the sum of Sales within the previous period, which is being selected on a special data slicer for previous period.
If there were no sales in the country during the previous period, I get a blank value which is totally OK.

However, if there were no sales during the previous period, I don't need to show Sessions for such countries neither for the current nor for the previous period.
I made it possible by creating the following measure for Sessions:

Sessions = IF(ISBLANK(sales_table[sales]),BLANK(),
CALCULATE(SUM(sessions_table[sessions])))
Although this measure hides the Session count for countries with no sales in the previous period, the Total is calculated for ALL rows including the hidden. For example, it calculates the Total of both Session columns including DE, which had no Sales within the previous period, but I don't actually need it in this case.

How should I modify the Session measure to avoid including the hidden values into the calculation of Totals?



Nikola123
New Member

yyyyyeeeez man, saved my life and my monitor! 

 

Thank you, This was bothering me for a month!

 

bchager6
Super User
Super User

@Greg_Deckler  I am thinking that this is going to be the solution to what I'm facing as well, but wonder if you can assist on how I'd integrate HASONEVALUE in to the measure I'm working with:

YTD Actuals =
CALCULATE(
SUM([ytd actuals]),
ALL('project '[active]),
FILTER('_fact_date',[ytd_dates] = 1)
)

Any thoughts?

@bchager6 Hard to tell, I would need to understand how your visualization is laid out. This article might get you where you need to be however: 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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Many kudos, @Greg_Deckler ! That is one of the most useful solutions I have come across all year. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors