I have 2 tables in my data model. One, named 'Data Table', has 4 fields: Date, Index, Category and Value. The other is a calendar. These tables are joined on the Date field.
I have a pivot table which uses my data model and has 2 slicers applied to it: 'Data Table'[Category] and Calendar[Date (Year)]. This pivot table has Index as the rows, and [Sum of Value] in the values.
I want to use a measure which will show the average of Value for all selected values (so, average of Value where 'Data Table'[Category] = {Category slicer selection} and 'Data Table'[Date] = {Calendar[Date] slicer selection}) . I am currently using =CALCULATE(AVERAGE('Data Table'[Value]),ALLSELECTED()) for this.
This seems to produce the desired result, however the pivot table will display all of the 'Index' rows, ignoring any calendar slicers, with [Sum of Value] producing blank values for each row where the calendar filter applies.
Without Measure
With Measure
I would like to be able to adjust this so that the additional rows do not appear. Hopefully this all makes sense, please let me know if any clarification is required though. Thanks in advance.
I have a pivot table which uses my data model and has 2 slicers applied to it: 'Data Table'[Category] and Calendar[Date (Year)]. This pivot table has Index as the rows, and [Sum of Value] in the values.
I want to use a measure which will show the average of Value for all selected values (so, average of Value where 'Data Table'[Category] = {Category slicer selection} and 'Data Table'[Date] = {Calendar[Date] slicer selection}) . I am currently using =CALCULATE(AVERAGE('Data Table'[Value]),ALLSELECTED()) for this.
This seems to produce the desired result, however the pivot table will display all of the 'Index' rows, ignoring any calendar slicers, with [Sum of Value] producing blank values for each row where the calendar filter applies.
Without Measure
| Index | Sum of Value |
|---|---|
| 3 | 38.38423414 |
| 4 | 3.200046329 |
| 8 | 95.27267842 |
With Measure
| Index | Sum of Value | AverageAll |
|---|---|---|
| 3 | 38.38423414 | 45.6189863 |
| 4 | 3.200046329 | 45.6189863 |
| 8 | 95.27267842 | 45.6189863 |
| 10 | 45.6189863 | |
| 12 | 45.6189863 | |
| 14 | 45.6189863 | |
| 15 | 45.6189863 |
I would like to be able to adjust this so that the additional rows do not appear. Hopefully this all makes sense, please let me know if any clarification is required though. Thanks in advance.
