Forum Discussion
Problem aggregating data at different levels
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 | 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 |
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
2 Replies
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
