topic Re: calculate total from comparing two difrent measure in DAX Commands and Tips

calculate total from comparing two difrent measure

a-tlantis — Wed, 25 Sep 2024 06:35:09 GMT

hello everyone

i have two measures that they are from two diffrent tables and then i have third measure that it values comes from comparison from those two measures.the challange i have is when i want to have total of third measure power bi gives the wrong number.

this is data.idea is when sum of amount for specific recep is more than maxamount in table b and maxamount is not zerro the third measure should be maxamount value otherwise should be sum of amount . my challange is total of the third measure .

table a

id recep amount date

1 a-40 200 2023-02-05

2 a-40 20 2023-03-01

3 a-50 100 2023-05-01

4 a-50 70 2023-06-22

5 a-60 10 2023-04-17

table b

id recep maxamount

1 a-40 1000

2 a-50 50

3 a-60 0

Re: calculate total from comparing two difrent measure

PhilipTreacy — Wed, 25 Sep 2024 06:41:02 GMT

a-tlantis

Please provide your measures, hard to solve this without seeing what your calculations are doing.

Regards

Phil

Re: calculate total from comparing two difrent measure

a-tlantis — Wed, 25 Sep 2024 06:57:34 GMT

first measure = sum ( amount ) based on recep column

second measure = sum (maxamount) based on recep column

Re: calculate total from comparing two difrent measure

PhilipTreacy — Wed, 25 Sep 2024 08:43:23 GMT

Hi a-tlantis

Download my PBIX file with the example below

If you provide an example of exactly what you expect as a result that would be helpful, but I think I get it.

Try this

Measure 1 = SUM(TableA[amount])

Measure 2 = CALCULATE(SUM('TableB'[maxamount]), 'TableB'[recep] = SELECTEDVALUE('TableA'[recep]))

Measure 3 = IF([Measure 1] < [Measure 2], [Measure 1], IF([Measure 2] = 0, [Measure 1], [Measure 2]))

NOTE: the two tables are related on the recep column.

Regards

Phil

Re: calculate total from comparing two difrent measure

a-tlantis — Wed, 25 Sep 2024 09:17:53 GMT

THANKS BUT MY ISSUE IS WHEN I WANT THE TOTAL OF THE THIRD MEASURE POWER BI GIVE ME WRONG NUMBER

Re: calculate total from comparing two difrent measure

PhilipTreacy — Wed, 25 Sep 2024 10:45:54 GMT

a-tlantis

Without you explaining what you mean by wrong number and showing an example of what you expect as the right number, it's hard to give you a solution.

What exactly is wrong with the table of data in my solution? What do you want as the values in that column for Measure 3?

Regards

Phil

Re: calculate total from comparing two difrent measure

Anonymous — Thu, 26 Sep 2024 03:42:22 GMT

Hi PhilipTreacy ,thanks for the quick reply, I'll add more.

Hi a-tlantis ,

Are you referring to the error here?

Try modifying your third measure

Measure 3 = VAR _result = IF ( [Measure] > [Measure 2] && [Measure 2] <> 0, [Measure 2], [Measure] ) VAR _table = ADDCOLUMNS ( SUMMARIZE ( 'Table2', [recep], "Measure1", [Measure], "Measre2", [Measure 2] ), "Measure3", IF ( [Measure1] > [Measre2] && [Measre2] <> 0, [Measre2], [Measure1] ) ) RETURN IF ( ISBLANK ( SELECTEDVALUE ( Table2[recep] ) ), SUMX ( _table, [Measure3] ), _result )

Best Regards,
Wenbin Zhou