DAX Measure based on multiple fields

[hbgv123]

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

[wdx223_Daniel · Accepted 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"))