Slicer to filter aggregated data

[Anonymous]

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?

[v-cherch-msft]

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

[Anonymous]

I have attached a very simple version of the issue I am working on.  

https://1drv.ms/u/s!Am--E_G2VxhMg9U15KQ_6r4abTWq-A

https://1drv.ms/x/s!Am--E_G2VxhMg9U0-csvk20iWhIL4A

[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

[Anonymous]

Thank you, @v-cherch-msft.  I think this almost gets me to where I need to be.  The final piece is getting this to work with my filters.  In my real data set, I am filtering by week.  So, what would I add to the formula you provided that would utilize the filters I have on my view?

[v-cherch-msft]

Hi @Anonymous

Add the week column as sample. You can get the average with calculated column or measure. If it is not your case, please show more about your real data.

Column 2 =
AVERAGEX (
    FILTER (
        Sheet1,
        Sheet1[Customer] = EARLIER ( Sheet1[Customer] )
            && Sheet1[week] = EARLIER ( Sheet1[week] )
    ),
    Sheet1[COMPLETE ORDER %]
)

Measure  = CALCULATE(AVERAGE(Sheet1[COMPLETE ORDER %]),ALLEXCEPT(Sheet1,Sheet1[Customer],Sheet1[week]))

Regards,

Cherie

[Anonymous]

The example I gave was simplified.  In reality, I need something that will adjust to all of my filters.  I am surprised this is so difficult to achieve.

[v-cherch-msft]

Hi @Anonymous

It seems you may try to use ALLSELECTED Function. Below is the article for your reference.

https://www.sqlbi.com/articles/understanding-allselected/

Regards,

Cherie

[Anonymous]

Yes, ALLSELECTED does seem to be the solution, but I have been trying every way possible to use it with the AVERAGEX function and I am have no success.  To me, the formula should look something like this:

XYZ =
AVERAGEX( FILTER(
    ALLSELECTED( QA, QA[CARRIER]) = EARLIER ( QA[CARRIER] ),
   QA[TOTAL SCORE]
))

But I get an error on this formula saying that too many arguments were passed to the ALLSELECTED function.  I have also tried it with CALCULATE and several other ways with no success.

[v-cherch-msft]

Hi @Anonymous

You may have a look at below post. It seems the syntax for ')' is incorrect. It should be ALLSELECTED( QA )

https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/td-p/362637

Regards,

Cherie

[Anonymous]

Thanks for you efforts on this, @v-cherch-msft, but it appears as though nothing is going to work.  I got the formula to work with no errors, but it just gives me the same result as the original formula and does not change with filters.  

There has to be some way to filter on aggregates.  If I was looking at a category like sales, for instance, and I only wanted to see certain products or customers or regions over a certain total sales value, there just has to be a way to do it.  That seems pretty fundamental for a BI program.

[v-cherch-msft]

Hi @Anonymous

Maybe it's better that you could share more data sample which could reproduce your scenario and your desired output. One way to filter on aggregates is visual level filter or page level filter. Not sure if it could help you. Others should be some measures.

https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter

Regards,

Cherie