cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter after calculation in barchart

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

table1.JPG

 

Then I can make this barchart

barchart1.JPG

 

 

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

barchart2.JPG

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]

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter after calculation in barchart

Hi @Anonymous,

 

Please try the function ALL rather than ALLSELECTED.

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

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
quentin_vigne Senior Member
Senior Member

Re: Filter after calculation in barchart

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
Not applicable

Re: Filter after calculation in barchart

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 Smiley Sad 

 

quentin_vigne Senior Member
Senior Member

Re: Filter after calculation in barchart

@Anonymous

 

Can you provide us a sample of data ? 

 

- Quentin

Community Support Team
Community Support Team

Re: Filter after calculation in barchart

Hi @Anonymous,

 

Please try the function ALL rather than ALLSELECTED.

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

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Filter after calculation in barchart

Hi,

 

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