Forum Discussion
Slicer to filter aggregated data
I have a column with a formula that says if COLUMN A is complete, then 1 and if it is blank, then zero.
COLUMN A COMPLETE = IF('QA'[COLUMN A]<>"",1,0)
I can then add the values in this column to get the total complete and then I can calculate the % complete. This is done by customer so at the end, I will have a table something like:
Customer 1 90%
Customer 2 82%
Customer 3 71%
Customer 4 32%
etc.
I would like to create a slicer that will filter the customers based on percentage complete. However if I create a slicer based on COLUMN A COMPLETE %, it filters based on the individual cells and not the aggregate per customer. Since that column only has 2 potential values, 0 or 1, if I move the slicer above 0%, it filters out all of the 0s, so that every customer will show 100%.
How do I filter on the aggregate?
11 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
It seems you may try to add an index column for your table then filter it. If it is not your case, please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.
Regards,
Cherie
I have attached a very simple version of the issue I am working on.
- v-cherch-msft
Hi Anonymous
There is no better way to use slicer to filter aggregrated data. I would recommend that you may add a column to get the value. Then use it as slicer because only column value can be used in slicer.
Column = AVERAGEX ( FILTER ( Sheet1, Sheet1[Customer] = EARLIER ( Sheet1[Customer] ) ), Sheet1[COMPLETE ORDER %] )Regards,
Cherie