Filter measures results

[Fox5]

Hi everyone

I use measures to calculate values A, B and C (the sum of value A + B) as shown in the table below. Now, I need to summarize the positive and negative Values C to create a key indicator (i.e. 1+[sum neg C / sum pos C]). How can this be achieved?

Value A (Result of Measure A)	Value B (Result of Measure B)	Value C (Result of Measure A+B)
500	-600	-100
1000	-500	500
2000	-1500	500

Sum of positive Value C = 1000

Sum of negative Value C = -100

1+(1000/-100) = 0.9

[amitchandak]

@Fox5 , You need use one or more group by for that

Divide(

Sumx(filter(Summarize(Table, Table[Column], "_1", [C]),[C] >0) ,[C]),

Sumx(filter(Summarize(Table, Table[Column], "_1", [C]),[C] <0) ,[C])

)

[Fox5]

Thanks for your reply @amitchandak 

Using the DAX formula "Sumx(filter(Summarize(Table, Table[Column], "_1", [C]),[C] >0) ,[C])" results in the sum of C. Based on my exaple, the result is 900 instead of 1000.

Using the DAX formula "Sumx(filter(Summarize(Table, Table[Column], "_1", [C]),[C] <0) ,[C])" results in 0, instead of -100

[v-kkf-msft · Accepted solution]

Hi @Fox5 ,

You need to use the columns added in the visual where the MeasureC is located for the SUMMARIZE function. 

Measure = 
var tab = SUMMARIZE('Table','Table'[Column1],'Table'[Column2],"C1",[MeasureC])
return SUMX( FILTER( tab, [C1] < 0 ), [C1])

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fox5]

You're a genius 😄. Thank you very much.