Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
quentin_vigne
Solution Sage
Solution Sage

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

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 😞 

 

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.

@Anonymous

 

Can you provide us a sample of data ? 

 

- Quentin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.