Help with DAX

[Singh_Yoshi]

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

[Anonymous]

Hi, @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.

[Singh_Yoshi]

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 DAX

[Anonymous · Accepted solution]

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.

[Singh_Yoshi]

@Anonymous     Everything is perfect. This is almost what I expected. But the Please modify Result2 formula alone as below.

MSRP of FJ16 / SUM(MSRP of FJ11 + Sum Measure)

eg: 1580000/(1392000+(-1674)) = 1.1%

[manvishah17]

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
) * 100

[Kedar_Pande]

@Singh_Yoshi 

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

Percentage = 
DIVIDE([Difference], [LC], 0)