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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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.

 

 

 

 

 

 

 

 



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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
108 REPLIES 108
jfsanchez
Frequent Visitor

.

bibee
Regular Visitor

Hello @Greg_Deckler  @Ashish_Mathur @Otto_Luvpuppy , 

 

I am lookin at this thread , to solve a similar problem and I tried the Sumx soln as well has HASONEVALUE but overall sum I am still not able to sum up to expected. Can you please Advise what wrong with the my formula . 

Source Data(Calculation in Excel)  :

DistriEAN Local Actual ForecastMAEWeightMAPE x WeightWMAPE
BD4740011550719.2517.564429%3.1779%0.28%16%
BD47400179240116.0995122.7436%19.1665%1.10% 
BD4740017934934.44125.1486327%5.6858%1.53% 
BD30142600078362.00164.536926127%0.3304%0.42% 
BD30142600078674.39245.48000125%0.7251%0.18% 
BD30142607810331.33445.009387275%0.2203%0.61% 
BD490243004423332.4667230.182357%5.3598%0.38% 
BD490243004425726.000317.2386234%4.2923%1.45% 
BD4902430102247227.6804247.69159%37.5871%3.30% 
BD4902430102254100.3049128.697228%16.5591%4.69% 
BD490243011865116.1025112.5100522%2.6583%0.59% 
BD490243018893713.6665320.0886947%2.2562%1.06% 
BD4902430188951128.7106727%1.9810%0.54% 
      16% 

 

Ouput in PowerBI :

bibee_0-1660592847603.png

 

Using SUMX:

MAPE * Weight =
VAR _MAE = CALCULATE(IF(temp_ean[Measure_Actual]=0,1,ABS(temp_ean[Measure_Actual]-temp_ean[Measure_Forecast])/temp_ean[Measure_Actual]))
VAR _WEIGHT = DIVIDE(temp_ean[Measure_Actual] , temp_ean[TotalActual])
RETURN

SUMX(VALUES(temp_ean[Distro]) , _MAE * _WEIGHT)

Using HASONEVALUE:
Measure1 =
VAR __table = SUMMARIZE(temp_ean,temp_ean[Distro],"__value",temp_ean[MAPE * Weight])
RETURN
IF (
    HASONEVALUE(temp_ean[Distro]),
    temp_ean[MAPE * Weight],
    SUMX(__table,[__value])
    )

Any help is appreciated!

Thanks

Hi,

It is very difficult to help you like this.  Describe the question and share the download link of your PBI file.  Also share the download link of your MS Excel file with your formulas there so that your logic can be understood and translated into the DAX language.


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

Buen dia, me considero nuevo en esto, 

si me puede ayudar por favor, tengo la siguiente tabla

jfsanchez_0-1660785094150.png

los archivos estan relacionados entre si.

1.- el MON. PPTO lo obtengo de un archivo, de la columna de totales

2.- el MON. REAL lo obtengo de otro archivo, de la columna de totales

3.- la diferencia la obtengo de la resta entre estas dos columnas de diferentes archivos usando esta medida -- 

DIFERENCIA = SUM('PRESUPUESTO DIRECTO'[MON_TOTAL]) - SUM('COSTO DIRECTO'[MON_TOTAL])
(si se pudiera una solucion desde esta medida para que me sume solo los positivos seria excelente)
 
el problema es que quiero obtener una columna mas con la suma unicamente de los valores positivos, que es el dato de la columna sin color en la imagen, el resultado de la medida es el correcto pero no el esperado por mi persona, ya que la medida esta haciendo su funcion lineal. 
esta es la medida usada
PRESUPUESTO POR EJECUTAR = if(SUMX('PRESUPUESTO DIRECTO','PRESUPUESTO DIRECTO'[MON_TOTAL]) > sum('COSTO DIRECTO'[MON_TOTAL]),SUMX('PRESUPUESTO DIRECTO','PRESUPUESTO DIRECTO'[MON_TOTAL]) - SUM('COSTO DIRECTO'[MON_TOTAL]))
 
ME PODRIA AYUDAR DANDOME UNA SOLUCION PARA QUE EL TOTAL ME SUME UNICAMENTE LOS VALORES POSITIVOS, YA REALICE VARIAS MEDIDAS PERO NINGUNA ME DA EL RESULTADO ESPERADO,  ESPERO DARME A ENTERDER
debasnhu
Frequent Visitor

@Greg_Deckler  Hasonevalue is working if we are dealing with Existing Colomn I have tried on top of my measure its not working as x function we can not use in case of masure.

My problam is:-

1) we have two masure in table visualization with total like masure A & B

2) requirement is if (A-B<0,0,A-B), so i need to takeout the all +ve value from A-B(and assign -ve and 0 as 0), Then the total should be there,I am able to achive The above but Total is wrong (when i am adding all values indivually total is different which is actual )

using following masure----

masure C= A-B, Total = SUMX(FILTER(ALLSELECTED(Table[column]),[C]>0),[C]) note-: Working wrong at Total level

here Table[column] is ref. coloum for row filter... Thanks 

it works for me after doing some R&D.

measure C = meaasure A- Measure B.

+ve total = Calculate([C],FILTER(SUMMARIZE(FACTTABLE,PARENTDIMTABLE[COLUMN],FACTTABLE[COLUMN]),[C]>0))

zahirr
Regular Visitor

 

I want to calculate this 

zahirr_0-1650980093658.png

 

And created the following measure - 

But says somewhere is wrong, but can't find it 😞 

 

 

IF(SUM ( 'MasterTbl'[Sett] ) = 1,

                                SUMX (
                (IF (
            SUM ( 'MasterTbl'[Sett] ) = 0,
            CALCULATE (
                SUM ( 'MasterTbl'[Total number of beneficiaries] ),
                ALLEXCEPT ( MasterTbl, 'MasterTbl'[Activities and Indicators.Activities] )
            ) )),

                 IF

            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            )) )) >= SUM('Settlement name'[population]), SUM('Settlement name'[population])),
            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            ) ) ) ) ),

                            (   IF (
            SUM ( 'MasterTbl'[Sett] ) = 0,
            CALCULATE (
                SUM ( 'MasterTbl'[Total number of beneficiaries] ),
                ALLEXCEPT ( MasterTbl, 'MasterTbl'[Activities and Indicators.Activities] )
            ),
    IF
        (
            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            )
                        )
                    )
            ) >= SUM('Settlement name'[population]), SUM('Settlement name'[population]),
            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            ) )  ) ) )  )))

Otto_Luvpuppy
Helper II
Helper II

Hi Greg,
I realise this is an old post but it seems to still be, very much, relevent as I know loads of people who struggle with this concept.

I've tried to replicate the method you use for my own problem and kind of get half way but then hit the gravel.
Is there any chance you could apply your process to the issue I posted?
https://community.powerbi.com/t5/Desktop/Totals-Calculations-using-a-a-real-problem/m-p/2250407

Thanks

Paul

Anonymous
Not applicable

Hi everyone, 

 

I'm also having a problem with column totals with the following measure:

 

Zusatzumsatz = 
 
VAR Zielgruppe = 
    CALCULATE(
    [Umsatz pro Käufer],
    FILTER('Table1''Table1'[CONTROLGROUP] = "Zielgruppe")
    )

VAR Datum =
    MAX(Table1[SENDDATE])

VAR ProgrammID = 
    MIN(Table1[Program ID])

VAR Kontrollgruppe =
    CALCULATE(
    [Umsatz pro Käufer],
    Table2[PROGRAMID] = ProgrammID,
    Table1[SENDDATE] = Datum,
    Table1[CONTROLGROUP] = "Kontrollgruppe",
    ALL()
    )

VAR IstZielgruppe =
    MAX(Table1[CONTROLGROUP])="Zielgruppe"

VAR Auflage_Final = 
    CALCULATE( SUM(Table1[AUFLAGE]), KEEPFILTERS(Table1[CONTROLGROUP]="Zielgruppe"))

RETURN
    IF(IstZielgruppe,
    (Zielgruppe - Kontrollgruppe)* Auflage_Final
    )

Michella_0-1637922501035.png

As you can see - the column total is wring. I understand that the measure is calculating in row and not column context. Is there a way that the calculation in the value part of the table is evaluated per row but the total is just the sum of the column?

Hi,

Drag this measure to your visual

Measure = SUMX(VALUES(Data[kampagnenname]),[Zusatzumsatz])

Hope this helps.


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

Good Morning.

 

I'm also having a similar problem in that the total for the measure i've created isn't adding up 

 

DanH2000_0-1634812496359.png

I have tried adding the "IsFiltered" and "SUMX"  functions within my measurement however so far haven't had success (the measurement does contain switch statements too) can you possibly advise? The DAX I'm using for the original measurement is below,

Measure = IF(MAX('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])="Major",
IF(COUNT('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])>1,13,
SWITCH(
DATEDIFF(max('Core Data'[Go Live Date]),max('Date Table'[Date]),WEEK)
, -12,1,-11,2,-10,3,-9,4,-8,5,-7,6,-6,8,-5,10,-4,10,-3,10,-2,11,-1,12,0,12,1,12,2,12,3,12,4,12,5,11,6,10,7,9,8,8,9,7,10,6,11,4,12,2)
)
,
IF(max('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])="Medium",
IF(COUNT('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])>1,13,
SWITCH(
DATEDIFF(max('Core Data'[Go Live Date]),max('Date Table'[Date]),WEEK)
, -12,0,-11,1,-10,2,-9,3,-8,4,-7,5,-6,7,-5,8,-4,8,-3,8,-2,9,-1,10,0,10,1,10,2,10,3,10,4,9,5,9,6,8,7,7,8,6,9,5,10,4,11,2,12,1))
,

IF(max('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])="Minor",
IF(COUNT('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])>1,13,
SWITCH(
DATEDIFF(max('Core Data'[Go Live Date]),max('Date Table'[Date]),WEEK)
, -12,0,-11,0,-10,0,-9,1,-8,2,-7,3,-6,4,-5,5,-4,6,-3,7,-2,7,-1,8,0,8,1,8,2,8,3,7,4,6,5,6,6,5,7,4,8,3,9,2,10,1,11,0,12,0))

)))
zeelongtime
Frequent Visitor

Hi there, Im having problem with the Grand Total for the Allocation Column.

zeelongtime_0-1632518630808.png

The idea is when the 2021 Allocation has no value to give me the average else if tere is value just give me that value. The formula is :

=IF(HASONEVALUE(Data[202109 Allocation Tons]),SUM(Data[4 Months Average]),SUM([202109 Allocation Tons]))

which provide the correct value at the cell level but the total is ot accurate.

Thanks for the help.

 

Hi,

Share the link from where i can download your PBI file and show the expected answer very clearly.


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

jonnyA_0-1631648021468.png

Anyone have any advice on how I can get this to sum correctly?

 

Rows:

  • Provider Name
  • CPT Code

Columns

  • DOS - Month

Values

  • CPT Code (Count Distinct)

 

@jonnyA Have you looked at this? Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 



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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
alamhaz
Helper I
Helper I

Hi 

In my case its works only when i choose some dates or product filter but when i dont use any filter its not showing total values 

check the screen shot. ss2.JPGss1.JPG

measure is 

Avg Sale lost (total ) = IF(HASONEFILTER('Product Movement'[Date]), IF([Total Qty Diff Avg] > 0, [Total Qty Diff Avg] * [Avg Selling Price (PV)]),SUMX(VALUES('Product Movement'[Date]), [Avg Sale Lost]) )
Please help

Hi,

In your visual, the Date field should actually be dragged from the Calendar Table.  I can help, if you explain the question and show the expected result.  Please also share the link from where i can download your PBI file. 


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

thanks i figure out the problem, thanks

Anonymous
Not applicable

Hi,

I have an issue with summarizing multiple measures. This is my Measure :
Somme des mesures = CALCULATE(

  SUMX(VALUES('Calendar'[Add on]),'Calendar'[Add on])

+ SUMX(VALUES('Calendar'[Indirect Manufacturing]),'Calendar'[Indirect Manufacturing])

+ SUMX(VALUES('Calendar'[Royalties]),'Calendar'[Royalties])

+ SUMX(VALUES('Calendar'[Overhead]),'Calendar'[Overhead])

+ SUMX(VALUES('Calendar'[Bank position]),'Calendar'[Bank position]))

 

And this is my cumulative Measure :

Cumulative Total Measure = CALCULATE([Total measure 1],

    FILTER(ALLSELECTED('Calendar'),'Calendar'[Week 3] <= MAX('Calendar'[Week 3]) && 'Calendar'[Week 3] >= DATE(2021,07,17))

)

 

It works properly for some rows but it stopped then :

tahar1407_1-1628082170601.png

 

 

I don't understand what's wrong and why my measure stopped 

 

Could you please help me

 

Thank you so much

BellaLauritsen
Regular Visitor

I have a similar problem and can't get it to show the correct total. 

I need to calculate Possible Turnover. 

 

I have a measure calculating quantity on sales order - quantity on purchase order. 

I have a table with all sales prices listed. 

 

I created a measure: 

SUMX('Sales Price Dim - SPD','Sales Price Dim - SPD'[Standard Price (LCY) - SPD]*[Available QTY (POT-SOT) - POT])
 
I have tried with the HASONEFILTER, but can't seem to succeed. Help 🙂 

 

mgiusto
Helper I
Helper I

I have a different issue with measures that I can't seem to figure out. I have a drop-down selector for a user to choose an Estimated Cost, I then refer to this in a measure like so: SelectedCostSELECTEDVALUE(PerEntryCost[EstimatedCost])

 

What I want to do next is divide this SelectedCost by the TotalEntries field in my grid below, I want to divide it by the 122 which is the total. Then when I have that value (which is 9.84) I want to use that 9.84 to multiply against the 92 in the first row and have my EstCost field show 905.28 and the second row multiply 9.84 by 30 to get 295.20. Then my final total of EstCost on the bottom row would show $1,200.00. 

 

Here are my measures:

SelectedCost = SELECTEDVALUE(PerEntryCost[EstimatedCost])

Total Entries = DISTINCTCOUNT(CHB_Declarations[CHBFileID])

PerEntryCost = [SelectedCost]/[Total Entries]

EstCost = [Total Entries]*[PerEntryCost]

 

As you can see below, the measure is being calculated at each row level not using the total of Total Entries, same goes for the PerEntryCost, I am expecting that to be 9.84 on each row. (I'm not going to be showing the per entry cost in the final solution, I just put it there to show the issue I am experiencing.) I really just need the EstCost to work the way I have described above.

 

PBI Measures.png

Some more deatil on this table view, this is based on a couple slicers of a date range and one customer, so if these filters are changed I need these measures to recalculate based on the new slicer selections.

 

I'm thinking this is probably pretty easy, I am just having a hard time figuring it out. I tried calculated columns and that didn't work either.  Thanks for any assistance!

 

Michael Giusto

@Greg_Deckler @Ashish_Mathur 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors