Question regarding fixed value

JustaRookie012 — Tue, 24 Oct 2023 12:47:30 GMT

Dear community,

I have the following simplified Table

Company	Project	Period	Value1	Value2
A	A	13	10	20
A	A	13	10	20
A	B	13		20
A	B	13		20
A	C	13	10	20
A	C	13	20	20
A	D	13		20
A	D	13		10

I want to create a measure that returns me a fixed value by dividing the sum of non blank value1 and the sum of non blank value2 whereas the sum of these values is grouped by Company and Period.

Here is what I have created to "achieve" the above desired result

AVERAGEX ( SUMMARIZE ( CALCULATETABLE ( 'Table', 'Table'[Value1] <> BLANK () ), 'Table'[Company], 'Table'[Period], "Result", VAR SumValue1= SUM ( 'Table'[Value1] ) VAR SumValue2 = SUM ( 'Table'[Value2] ) RETURN SumValue1/ SumValue2 ), [Result] )

And I get the following as a result if I use this measure, it seems that measure is calculating row by row

Company	Project	Period	Value1	Value2	Result
A	A	13	10	20	0,5
A	A	13	10	20	0,5
A	B	13		20	0
A	B	13		20	0
A	C	13	10	20	0,5
A	C	13	20	20	1
A	D	13		20	0
A	D	13		10	0
Total					0,33333

Sum(Value1) = 50

Sum(Value2) = 150

Result = 50/150= 0,333333

Whereas I expected the fixed value based on the sum of non blank value1 and sum of non blank value2 should be applied

Company	Project	Period	Value1	Value2	Result
A	A	13	10	20	0,3333
A	A	13	10	20	0,3333
A	B	13		20	0,3333
A	B	13		20	0,3333
A	C	13	10	20	0,3333
A	C	13	20	20	0,3333
A	D	13		20	0,3333
A	D	13		10	0,3333
Total					0,3333

Any help would be appreciated!

Kind regards,

Rookie12

Re: Question regarding fixed value

EylesIT — Tue, 24 Oct 2023 17:53:36 GMT

JustaRookie012, here is my suggested solution.

Change the DAX of your Result measure to this:

Result = CALCULATE( DIVIDE(SUM(YourTable[Value1]), SUM(YourTable[Value2])), ALL(YourTable) )

This gives me the following output:

topic Re: Question regarding fixed value in DAX Commands and Tips

Question regarding fixed value

Re: Question regarding fixed value