Forum Discussion
Create a slicer that filters summarised (sum) values
I have the dataset as below
Email Address | Dept ID | Alias | Emp Id | Voice Mins | SMS Msgs | # of Pages | Dispatch Mins | Data KB | Currency | Total Charges |
43425 | 903618 | 7 | 6 | 0 | 0 | 14583798 | USD | 115.94 | ||
43425 | 903618 | 4 | 4 | 0 | 0 | 8931175 | USD | 68.25 | ||
43425 | 903618 | 39 | 1 | 0 | 0 | 7964692 | USD | 57.56 |
In the table visual I would group and summarise this data grouped by Invoices and sum of total charges.
Email Address | Dept ID | Alias | Emp Id | Voice Mins | SMS Msgs | # of Pages | Dispatch Mins | Data KB | Currency | Total Charges | Number Of Invoices |
| 43425 | 903618 | 7 | 6 | 0 | 0 | 14583798 | USD | 241.75 | 3 |
Now I want to create a slicer for Total Charges >= input value.
When i input the slicer to filter for values >= 160, it should show me the summarised data as
Email Address | Dept ID | Alias | Emp Id | Voice Mins | SMS Msgs | # of Pages | Dispatch Mins | Data KB | Currency | Total Charges | Number Of Invoices |
| 43425 | 903618 | 7 | 6 | 0 | 0 | 14583798 | USD | 241.75 | 3 |
Instead I am getting no result, that is becasue it is applying filter on the original data set and not the sum of the grouped data.
It is checking for the total charges (115.94,68.25,57.56) and not the summed value shown in the table visual which is 241.75
Is there a way to get the slicer to filter values but on the grouped and summed data?
5 Replies
kotlonarendra
One of option is to create a DAX calculated table as follows and use the Total Charges column on the slicer to filter.SummarizedTable = SUMMARIZE( 'YourTable', 'YourTable'[Email Address], 'YourTable'[Dept ID], 'YourTable'[Alias], 'YourTable'[Emp Id], 'YourTable'[Voice Mins], 'YourTable'[SMS Msgs], 'YourTable'[# of Pages], 'YourTable'[Dispatch Mins], 'YourTable'[Data KB], 'YourTable'[Currency], "Total Charges (Sum)", SUM('YourTable'[Total Charges]), "Row Count", COUNTROWS('YourTable') )kotlonarendra
I am not sure how you implemented the proposed solution.
Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.Sure, let me prepare the data and send it you via GDrive
