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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ignas
Advocate II
Advocate II

Need help with IF statement displaying calculated measure in the table

Hello

 

I have input for Sales and Cost. Profit I estimate like this: Profit=Sales-Cost

Then I want to display all three accounts in the same table with if statement:

IF = SUMX(Account; IF(Account[Account]="Profit"; [Sales]-[Cost] ; [Amount+]))

 

Unfortunately, Profit account is not displayed. Does anybody know how I can display the calculated measure in the same table?

 

Here is the sample file: https://mega.nz/#!umBwWTgY!gxxwCcIWYWcGTW-Q07fCMQ_VUBpEcEmsS1FTcV4XHI4

Thank you a lot for the help

 

Cheers

Ignas

1 ACCEPTED SOLUTION

Hi ignas,

 

Please modify your measures like this:

Cost = CALCULATE(SUM(Data[Amount]), Account[Account]="Cost")

Sales = CALCULATE(SUM(Data[Amount]), Account[Account]="Sales")

IF = 
VAR total = SUMX(Data, Data[Amount])
RETURN
IF (
    MAX('Account'[Account]) = "Profit",
    [Sales] - [Cost],
    total
)

捕获.PNG 

 

PBIX here: https://www.dropbox.com/s/95vvuhe2iv3547g/IF_statement.pbix?dl=0

 

Regards,

Jimmy Tao

View solution in original post

7 REPLIES 7
v-yuta-msft
Community Support
Community Support

Hi ignas,

 

Modify you DAX formula like this and try again:

IF =
IF (
    'Account'[Account] = "Profit",
    SUMX ( 'Account', [Sales] - [Cost] ),
    SUMX ( 'Account', 'Account'[Amount+] )
)

Regards,

Jimmy Tao

Hey @

I really appreciate your attemp to help me. Unfortunately I cannot refer to Account column and I do not know the reason. Would you be able to have a look at my attached file? I do not know how to attach to the message so I dropped the file to file sharing: https://mega.nz/#!umBwWTgY!gxxwCcIWYWcGTW-Q07fCMQ_VUBpEcEmsS1FTcV4XHI4

Thanks a lot

 

Ignas

 

 

Hi ignas

 

Could you please provide decryption key of your file?

 

Regards,

Jimmy Tao

Hello Jimmy

 

Sorry for that. Here is a new link: https://mega.nz/#!umBwWTgY!gxxwCcIWYWcGTW-Q07fCMQ_VUBpEcEmsS1FTcV4XHI4

 

Hi ignas,

 

Please modify your measures like this:

Cost = CALCULATE(SUM(Data[Amount]), Account[Account]="Cost")

Sales = CALCULATE(SUM(Data[Amount]), Account[Account]="Sales")

IF = 
VAR total = SUMX(Data, Data[Amount])
RETURN
IF (
    MAX('Account'[Account]) = "Profit",
    [Sales] - [Cost],
    total
)

捕获.PNG 

 

PBIX here: https://www.dropbox.com/s/95vvuhe2iv3547g/IF_statement.pbix?dl=0

 

Regards,

Jimmy Tao

Hello @v-yuta-msft

 

Thanks again for your help.

I try to create something very similar except that instead of [Sales]-[Cost] I try to create:

#employees = DISTINCTCOUNT(Data[Employee])



Unfortunately, then I try to display with this formula it does not show anything

 

 

IF = IF (MAX(Account[KPI])="#employees";
[#employees];
[Sum_Amount])


Would you find please a couple of minutes to have a look at my dashboard and see why it does not display the desired result?
https://mega.nz/#!KjISgAqK!Qy0RjgYuxZGJJjR-e-2fJZLc5EpFYqIL24jEB6x2-Fk

 

Thanks a lot

Regards,


Ignas

Hello @v-yuta-msft


Thanks a lot. It is indeed the solution. I cannot understand MAX function if this context.

https://msdn.microsoft.com/en-us/query-bi/dax/max-function-dax
MAX is used for numeric values to get the maximum number. Why do we need MAX in this case?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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