Forum Discussion
DAX Measure based on multiple fields
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
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"))
1 Reply
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"))