topic How to get correct total of measure in DAX Commands and Tips

How to get correct total of measure

komald — Mon, 09 May 2022 07:08:38 GMT

I have created measure which shows revised target for a sales person dc(distribution channel)wise but i'm getting measure total incorrect.

dax for revised target
revised target =

Revised_target =

CALCULATE(Sum(TargetBySalesPersonAll[Target_Amount]),TargetBySalesPersonAll[Revision_No]=MAX(TargetBySalesPersonAll[Revision_No]))

similar way i have written dax for initial target it gives me correct total
Initial_Target=CALCULATE(Sum(TargetBySalesPersonAll[Target_Amount]),TargetBySalesPersonAll[Revision_No]=1)
what's wrong in revised_target dax ?

Re: How to get correct total of measure

amitchandak — Mon, 09 May 2022 08:23:29 GMT

komald , if you use row context you need build total using visual context

refer example

Revised_target =

sumx( Addcolumns( summarize(table, Table[person id], table[reveison] ), "_1"
CALCULATE(Sum(TargetBySalesPersonAll[Target_Amount]),filter(allselected(TargetBySalesPersonAll), TargetBySalesPersonAll[Revision_No]=MAX(TargetBySalesPersonAll[Revision_No])) ), [_1])

https://www.youtube.com/watch?v=ufHOOLdi_jk

Re: How to get correct total of measure

v-cazheng-msft — Fri, 27 May 2022 08:46:13 GMT

Hi komald,

Measure will calculate and generate results per the calculation context. At the same time, the total of measure will do calculations as well and it may be impacted by the functions in your dax formula. So to make the total correct, you can simply create another Measure to help you correct the total.

CorrectTotal = VAR midT = ADDCOLUMNS ( TargetBySalesPersonAll, "Revised", CALCULATE ( TargetBySalesPersonAll[Revised_target], VALUES ( TargetBySalesPersonAll[DCCode] ) ) ) RETURN SUMX ( midT, [Revised] )

Here are some blogs/links may help you better handle such kind of incorrect Measure total as well.

Solved: Measure total incorrect - Microsoft Power BI Community

Fixing Incorrect Totals Using DAX Measures In Power BI | Enterprise DNA

Power BI: Totals Incorrect and how to Fix it - Finance BI (finance-bi.com)

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know how your tables look like, the relationships you create for them and how you create these two count measures. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun