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 Code MSRP FJ11 1392000 FJ16 1580000 FJ7 1762000 FJ8 1912000 FJ15 1937000 FJ12 2279000   I have one measure in my dashbo...
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

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.