DAX Measure based on multiple fields

hbgv123 — Wed, 06 Sep 2023 06:32:50 GMT

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

Re: DAX Measure based on multiple fields

wdx223_Daniel — Wed, 06 Sep 2023 07:44:18 GMT

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

topic DAX Measure based on multiple fields in DAX Commands and Tips

DAX Measure based on multiple fields

Re: DAX Measure based on multiple fields