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.

Singh_Yoshi's avatar
Singh_Yoshi
Helper I
1 year ago

Hi,

This is one table called Table1,

Model CodeMSRP
FJ111392000
FJ161580000
FJ71762000
FJ81912000
FJ151937000
FJ122279000

 

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's avatar
Anonymous
Not applicable
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.

  • Singh_Yoshi's avatar
    Singh_Yoshi
    Helper I
    1 year ago

    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%