Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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
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
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
Proud to be a Super User!
THANKS BUT MY ISSUE IS WHEN I WANT THE TOTAL OF THE THIRD MEASURE POWER BI GIVE ME WRONG NUMBER
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
Proud to be a Super User!
Please provide your measures, hard to solve this without seeing what your calculations are doing.
Regards
Phil
Proud to be a Super User!
first measure = sum ( amount ) based on recep column
second measure = sum (maxamount) based on recep column
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |