Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 )
PBIX here: https://www.dropbox.com/s/95vvuhe2iv3547g/IF_statement.pbix?dl=0
Regards,
Jimmy Tao
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 @v-yuta-msft
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
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 )
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |