Help with DAX

[Singh_Yoshi]

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

[v-yiruan-msft · Accepted solution]

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]

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.