Help on converting Qlik calcuation to DAX

[Venkat_Thota]

I have the below data

RptMonth	RptDate	RptIndicator	Metric1	Metric2
2024 10	10/18/2024	1	0	16
2024 10	10/18/2024	2	0	0
2024 10	10/19/2024	1	0	13
2024 10	10/19/2024	1	0	2
2024 10	10/20/2024	1	0	9
2024 10	10/20/2024	2	0	8
2024 10	10/21/2024	1	0	16
2024 10	10/21/2024	1	0	0
2024 11	11/1/2024	1	0	18
2024 11	11/1/2024	2	0	11
2024 11	11/2/2024	1	0	14
2024 11	11/2/2024	2	0	5
2024 11	11/3/2024	1	0	16
2024 11	11/3/2024	1	0	40
2024 11	11/4/2024	2	0	21
2024 11	11/4/2024	1	0	4
2024 10		3	5	0
2024 10		3	2	0
2024 11		3	4	0
2024 11		3	6	0

I need the final outcome like this:

RptDate	Num	Denom	FinalMetric
10/18/2025	16	7	2.285714286
10/19/2024	15	7	2.142857143
10/20/2024	9	7	1.285714286
10/21/2024	16	7	2.285714286
11/1/2024	18	10	1.8
11/2/2024	14	10	1.4
11/3/2024	56	10	5.6
11/4/2024	4	10	0.4

Num is Metric2 total for RptIndicator 1

Demom is Metric1 total for RptIndicator 3 grouped for RptMonth

FinalMetric is Num devided by Denom

I am not able to get the month totals (Denom) as there was blanks in RPT Date. its coming as whole year total.

[bhanu_gautam · Accepted solution]

@Venkat_Thota , Create a calculated column for Num:

Num =
CALCULATE(
SUM('Table'[Metric2]),
'Table'[RptIndicator] = 1
)

Create a calculated column for Denom:

Denom =
CALCULATE(
SUM('Table'[Metric1]),
'Table'[RptIndicator] = 3,
ALLEXCEPT('Table', 'Table'[RptMonth])
)

Create a calculated column for FinalMetric:

DAX
FinalMetric =
DIVIDE(
'Table'[Num],
'Table'[Denom]
)

To ensure that the Denom calculation is correctly grouped by RptMonth and not affected by the blank RptDate, you can use the ALLEXCEPT function to ignore all filters except for RptMonth.

[v-zhouwen-msft · Accepted solution]

Hi all,thanks for the quick reply, I'll add more.

Hi @Venkat_Thota,

Please refer to the attachment for detailed information.

Best Regards

[Venkat_Thota]

Thanks @v-zhouwen-msft . this is also helpful and I am able to resolve my issue.

[speedramps]

We want to help but your description is bit vague. 😀

Try write it more clearly.

Use meaningful field names.

Provide example input data, desired output and clear step by step description.

Try use the same fieldnames and terminology in the input, output and description.

Keep it simple and minimal. Omit anthing that is not required, and format dates correctly.

Dont confuse things by including output measures in the input file. 

 Thanks

[bhanu_gautam · Accepted solution]

@Venkat_Thota , Create a calculated column for Num:

Num =
CALCULATE(
SUM('Table'[Metric2]),
'Table'[RptIndicator] = 1
)

Create a calculated column for Denom:

Denom =
CALCULATE(
SUM('Table'[Metric1]),
'Table'[RptIndicator] = 3,
ALLEXCEPT('Table', 'Table'[RptMonth])
)

Create a calculated column for FinalMetric:

DAX
FinalMetric =
DIVIDE(
'Table'[Num],
'Table'[Denom]
)

To ensure that the Denom calculation is correctly grouped by RptMonth and not affected by the blank RptDate, you can use the ALLEXCEPT function to ignore all filters except for RptMonth.

[Venkat_Thota]

Thanks @bhanu_gautam . I did same calcuation but didnt work. The only differnece is that I created all as calculated measures. but now as you suggested I created first two as Calculated columns and the last one as a calculated measure. That worked.