Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Singh_Yoshi
Frequent Visitor

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,

Singh_Yoshi_0-1735887382626.png

 


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

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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)

vyiruanmsft_0-1736152765495.png

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

vyiruanmsft_1-1736152784275.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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)

vyiruanmsft_0-1736152765495.png

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

vyiruanmsft_1-1736152784275.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rajendraongole1
Super User
Super User

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. 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.