Forum Discussion
Help with DAX
Hi All,
I have a following table called Table1,
| Model Code | MSRP | LC |
| FJ11 | 1392000 | |
| FJ16 | 1580000 | 188000 |
| FJ7 | 1762000 | 182000 |
| FJ8 | 1912000 | 150000 |
| FJ15 | 1937000 | 25000 |
| FJ12 | 2279000 | 342000 |
I have few measures as below names and consists of Items and Cost
- FJ11-16 - Sum of the measure is (-1674)
- FJ16-7 - Sum of the measure is (70650)
- FJ7-8 - Sum of the measure is (176150)
- FJ8-15 - Sum of the measure is (-68150)
- FJ15-12 - Sum of the measure is (28500)
1. Now I want to subtract LC cost - SUM of each measure
2. I need the percentage of (LC Cost - SUM of each measure)
Kindly help me with DAX code
Hi, Singh_Yoshi
I simply modeled a portion of the data and hope it fits your situation.
Pre MSRP = CALCULATE(MAX('Table'[MSRP]),FILTER(ALL('Table'),[MSRP]<SELECTEDVALUE('Table'[MSRP])))LC = IF([Pre MSRP]=BLANK(),BLANK(),SUM('Table'[MSRP])-[Pre MSRP])Sum Measure = SWITCH(TRUE(), SELECTEDVALUE('Table'[Model Code])="FJ11",BLANK(), SELECTEDVALUE('Table'[Model Code])="FJ16",[FJ11-16], SELECTEDVALUE('Table'[Model Code])="FJ7",[FJ16-7], SELECTEDVALUE('Table'[Model Code])="FJ8",[FJ7-8], SELECTEDVALUE('Table'[Model Code])="FJ15",[FJ8-15], SELECTEDVALUE('Table'[Model Code])="FJ12",[FJ15-12])Result1 = [LC]-[Sum Measure]Result2 = DIVIDE(SUM('Table'[MSRP]),[Pre MSRP]+[Sum Measure]*100)Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Difference =
VAR MeasureSum = SWITCH(
TRUE(),
[Model Code] = "FJ11-16", -1674,
[Model Code] = "FJ16-7", 70650,
[Model Code] = "FJ7-8", 176150,
[Model Code] = "FJ8-15", -68150,
[Model Code] = "FJ15-12", 28500
)
RETURN
[LC] - MeasureSumPercentage =
DIVIDE([Difference], [LC], 0)Hi Singh_Yoshi ,
Subtracted Value = SWITCH( TRUE(), SELECTEDVALUE(Table1[Model Code]) = "FJ11-16", Table1[LC] - [FJ11-16], SELECTEDVALUE(Table1[Model Code]) = "FJ16-7", Table1[LC] - [FJ16-7], SELECTEDVALUE(Table1[Model Code]) = "FJ7-8", Table1[LC] - [FJ7-8], SELECTEDVALUE(Table1[Model Code]) = "FJ8-15", Table1[LC] - [FJ8-15], SELECTEDVALUE(Table1[Model Code]) = "FJ15-12", Table1[LC] - [FJ15-12], BLANK() )Percentage = DIVIDE( [Subtracted Value], Table1[LC], 0 ) * 100Hi, Singh_Yoshi
Have you solved your problem? If not resolved, can you provide more example data and your desired output? This will better describe your problem. How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This is one table called Table1,Model Code MSRP FJ11 1392000 FJ16 1580000 FJ7 1762000 FJ8 1912000 FJ15 1937000 FJ12 2279000 I have one measure in my dashboard as below,
Now I want DAX for,
1. (MSRP of FJ16) / ((MSRP of FJ11 + Contents) * 100
2. (MSRP of FJ7) / ((MSRP of FJ16 + Contents) * 100
and so on...
please provide me DAXHi, Singh_Yoshi
I simply modeled a portion of the data and hope it fits your situation.
Pre MSRP = CALCULATE(MAX('Table'[MSRP]),FILTER(ALL('Table'),[MSRP]<SELECTEDVALUE('Table'[MSRP])))LC = IF([Pre MSRP]=BLANK(),BLANK(),SUM('Table'[MSRP])-[Pre MSRP])Sum Measure = SWITCH(TRUE(), SELECTEDVALUE('Table'[Model Code])="FJ11",BLANK(), SELECTEDVALUE('Table'[Model Code])="FJ16",[FJ11-16], SELECTEDVALUE('Table'[Model Code])="FJ7",[FJ16-7], SELECTEDVALUE('Table'[Model Code])="FJ8",[FJ7-8], SELECTEDVALUE('Table'[Model Code])="FJ15",[FJ8-15], SELECTEDVALUE('Table'[Model Code])="FJ12",[FJ15-12])Result1 = [LC]-[Sum Measure]Result2 = DIVIDE(SUM('Table'[MSRP]),[Pre MSRP]+[Sum Measure]*100)Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
