Forum Discussion

Singh_Yoshi's avatar
Singh_Yoshi
Helper I
1 year ago
Solved

Help with DAX

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Singh_Yoshi ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Add a index column in Power Query Editor

    = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

    2. Create a measure as below 

    Measure = 
VAR _index =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _curmsrp =
    SUM ( 'Table'[MSRP] )
VAR _nextmsrp =
    CALCULATE (
        SUM ( 'Table'[MSRP] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index + 1 )
    )
RETURN
    DIVIDE(_nextmsrp ,(( _curmsrp+[Contents])*100))

    If the above one can't help you figure out, please provide some raw data in your tables (exclude sensitive data) with Text format, the formula of measure [Contents] and your expected result with backend logic and special examples. You can add the required info in my attached pbix file.

    Best Regards

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Singh_Yoshi  -  you can create measures for each calculation as below way;

    Assuming that the "Contents" value is calculated elsewhere,

    Contents_Measure = SUM(TableWithContents[Contents])

     

    Use the CALCULATE function to dynamically filter the MSRP for each model individually as below:

     

    MSRP_FJ16 = CALCULATE(SUM(Table1[MSRP]), Table1[Model Code] = "FJ16")

    MSRP_FJ11 = CALCULATE(SUM(Table1[MSRP]), Table1[Model Code] = "FJ11")

    MSRP_FJ7 = CALCULATE(SUM(Table1[MSRP]), Table1[Model Code] = "FJ7")

     

    Now calculate the ratios as below for the above:

    Calculation_FJ16 =
    DIVIDE(
    [MSRP_FJ16],
    ([MSRP_FJ11] + [Contents_Measure]),
    0
    ) * 100

     

    another measure for , Calculation_FJ7 =
    DIVIDE(
    [MSRP_FJ7],
    ([MSRP_FJ16] + [Contents_Measure]),
    0
    ) * 100

    Replace Table1 with the actual name of your table containing the MSRP and model codes.
    Ensure that the "Contents" value is correctly linked or calculated in your data model.

    I hope the above logic helps. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Singh_Yoshi ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Add a index column in Power Query Editor

    = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

    2. Create a measure as below 

    Measure = 
VAR _index =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _curmsrp =
    SUM ( 'Table'[MSRP] )
VAR _nextmsrp =
    CALCULATE (
        SUM ( 'Table'[MSRP] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index + 1 )
    )
RETURN
    DIVIDE(_nextmsrp ,(( _curmsrp+[Contents])*100))

    If the above one can't help you figure out, please provide some raw data in your tables (exclude sensitive data) with Text format, the formula of measure [Contents] and your expected result with backend logic and special examples. You can add the required info in my attached pbix file.

    Best Regards