Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I am trying structure my calculations using DAX and i cannot change my datamodel.
I need to calculate two measures
Rating Measure - this is calculated by considering sum of above/ sum of under from format column when setting is Rating
Target Measure- this is calculated by considering sum of above/ sum of under from format column when setting is Target
I achieved this calculation in powerquery but i wanted to use DAX for other reasons.
Can you help in providing tips to achieve these measures?
Date | Product | Format | Setting | Value |
Jan-23 | Lux | Above | Rating | 20 |
Jan-23 | Lux | Under | Rating | 30 |
Jan-23 | Lux | Above | Target | 30 |
Jan-23 | Lux | Under | Target | 40 |
Feb-23 | Lux | Above | Rating | 30 |
Feb-23 | Lux | Under | Rating | 40 |
Feb-23 | Lux | Above | Target | 40 |
Feb-23 | Lux | Under | Target | 50 |
Mar-23 | Lux | Above | Rating | 45 |
Mar-23 | Lux | Under | Rating | 45 |
Mar-23 | Lux | Above | Target | 40 |
Mar-23 | Lux | Under | Target | 50 |
Expected solution for rating and target measure:
Thanks for your help
Solved! Go to Solution.
Rating Measure=DIVIDE(CALCULATE(SUM(Data[Value]),Data[Format]="Rating",Data[Setting]="Above"),CALCULATE(SUM(Data[Value]),Data[Format]="Rating",Data[Setting]="Under"))
Target Measure=DIVIDE(CALCULATE(SUM(Data[Value]),Data[Format]="Target",Data[Setting]="Above"),CALCULATE(SUM(Data[Value]),Data[Format]="Target",Data[Setting]="Under"))
Rating Measure=DIVIDE(CALCULATE(SUM(Data[Value]),Data[Format]="Rating",Data[Setting]="Above"),CALCULATE(SUM(Data[Value]),Data[Format]="Rating",Data[Setting]="Under"))
Target Measure=DIVIDE(CALCULATE(SUM(Data[Value]),Data[Format]="Target",Data[Setting]="Above"),CALCULATE(SUM(Data[Value]),Data[Format]="Target",Data[Setting]="Under"))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
32 | |
27 | |
24 | |
22 |