Filter after calculation in barchart

[Anonymous]

Hi,

I am trying to create a bar chart with a percentage of the total number that is bigger than the total count of all categories (because I have a blank value).

I tried using the DIVIDE function but because I don't want to include the blank value in my barchart it doesn't work. What is the trick? 

I need to filter AFTER the calculation. But as soon as I filter, the calculation changes.

This is the data in a table

Then I can make this barchart

But I don't want to show BLANK. So if I filter it out, it looks like this

Which is incorrect.

DAX:

N (in table) = DISTINCTCOUNT(Table[Column]) 

Total (in table) =   CALCULATE (DISTINCTCOUNT(Table[Column]);
ALLSELECTED(Table[TypeSecondPlacement]))

% (in table) = DISTINCTCOUNT(Table[Column]) / [Total]

[Ashish_Mathur]

Hi,

In the visual filter section, apply a non blanks condition on the TypeSecondPlacement field.

[quentin_vigne]

Hi @Anonymous

Your problem is that PBI doesn't understand that you total is in fact 61 and not 39. When he sum all your columns he have 2 + 22 + 4 + 11 = 39 

So 22/39 = 0.56 and this is correct.

If you want to include your 61 as total you need to add a measure = 61 or something like that ...

On your first chart it's working because you are displaying the percentage column with the TypeSecondPlacement, so PBI don't make calculations.

Hope this helped you understand

- Quentin

[Anonymous]

Hi @quentin_vigne,

Yes, that is exactly my challenge! Thanks for summarizing it so clearly. 

Still looking for a solution though!

My challenge is that I also have A LOT of filters, so the 61 also changes with the filters. I cannot just make a 61-measure 😞 

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please try the function ALL rather than ALLSELECTED.

Total (in table) =
CALCULATE (
    DISTINCTCOUNT ( Table[Column] );
    ALL ( Table[TypeSecondPlacement] )
)

Best Regards,

Dale

[quentin_vigne]

@Anonymous

Can you provide us a sample of data ? 

- Quentin