Problem aggregating data at different levels

[Anonymous]

Hello,

I am struggling for a couple of days with a problem that looked simple but I dont manage to solve. I would appreciate your help. 

I have a data set that looks like this: 

var1	var2	error_x	error_y	n_events
val1	val3	a	a	1000
val2	val3	a	a	135
val1	val3	a	b	12
val2	val4	a	c	1
val2	val4	b	a	39
val1	val5	b	b	100
val2	val4	b	b	85
val1	val4	c	c	2

var1 and var2 are not involved in the calculations I need to do, but they are relevant because I would like the calculations to take into account the filters that are applied to var1 and var2. Actually, I have managed to calculate what I need using "columns", but I need to use "measures" instead, so that the results take the filters into account. 

These are the columns that I have defined: 

error_x_agg = CALCULATE(sum('Table'[n_events]),ALLEXCEPT('Table', 'Table'[error_x]))

error_x_index = CALCULATE(sum('Table'[n_events]),ALLEXCEPT('Table', 'Table'[error_x], 'Table'[error_y]))/ 'Table'[error_x_agg]

error_x_index_norm = 'Table'[error_x_index]/CALCULATE(sum('Table'[error_x_index]),ALLEXCEPT('Table','Table'[error_y]))

These are the results that I get (error_x_agg and error_x_index: dont summarize, error_x_index_norm: sum):

error_x	error_y	error_x_agg	error_x_index	error_x_index_norm
a	a	1148	0.98867596	0.91907485
a	b	1148	0.01045296	0.00628848
a	c	1148	0.00087108	0.00087032
b	a	224	0.17410714	0.08092515
b	b	224	0.82589286	0.99371152
c	c	2	1	0.99912968

What I need is to do this but in such a way that if I filter some values out with var1 and var2, all the calculations are done only on the data that remains. As far as I understand (I am quite new to Power BI) I need to use measures instead of columns to achieve this. For example, if I filter with var1 = val1, I expect:

error_x	error_y	error_x_agg	error_x_index	error_x_index_norm
a	a	1012	0.98814229	1
a	b	1012	0.01185771	0.01171875
b	b	100	1	0.98828125
c	c	2	1	1

Buf if I try to do these calculations with measures instead of columns, I get problems with duplicated values of the aggregates. I have tried many different things without success. Can you please help me?

[Icey]

Hi @Anonymous ,

Not completely solved. I fail to make the third measure "error_x_index_norm" does not work. PBIX file attached.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws · Accepted solution]

Hi, @Anonymous ,

not sure what you are trying to do, but here is a possible strategy for you.

First create separate tables for Table[Var1] and Table[Var2], one table for each. Use these two tables for slicers. Then create measures like this:

error_x_agg =
IF (
    ISFILTERED ( Table_Var1[var1] );
    CALCULATE (
        SUM ( 'Table'[n_events] );
        FILTER (
            ALLEXCEPT ( 'Table'; 'Table'[error_x] );
            NOT ( 'Table'[var1] IN VALUES ( Table_Var1[var1] ) ) 
        )
    );
    CALCULATE ( SUM ( 'Table'[n_events] ); ALLEXCEPT ( 'Table'; 'Table'[error_x] ) )
)

This example only considers var1, you will have to see how you want to handle situations like: filter on Var1 and not Var2 vs filter on Var2 and not on Var1

Cheers,
Sturla