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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
a-tlantis
Frequent Visitor

calculate total from comparing two difrent measure

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

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

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

Hi @a-tlantis ,

Are you referring to the error here?

vzhouwenmsft_0-1727319884849.png

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
    )

vzhouwenmsft_1-1727322040466.png

 

Best Regards,
Wenbin Zhou

View solution in original post

6 REPLIES 6
v-zhouwen-msft
Community Support
Community Support

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

Hi @a-tlantis ,

Are you referring to the error here?

vzhouwenmsft_0-1727319884849.png

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
    )

vzhouwenmsft_1-1727322040466.png

 

Best Regards,
Wenbin Zhou

PhilipTreacy
Super User
Super User

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]))

 

PhilipTreacy_1-1727253693259.png

 

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

 

Regards

 

Phil

 

 

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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

@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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

@a-tlantis 

 

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

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


first measure       = sum ( amount ) based on recep column

second measure = sum (maxamount) based on recep column

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors