Forum Discussion

Singh_Yoshi's avatar
Singh_Yoshi
Helper I
1 year ago
Solved

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 FJ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

manvishah17's avatar
manvishah17
Solution Supplier
1 year ago

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