Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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
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
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.
Proud to be a Super User! | |
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |