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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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'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%