cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbuchan
New Member

Count Rows based on dynamic column value

Hi, I have a stacked bar chart with a line graph on and as a tooltip, I want to show the percentage value of each stack. I've tried various solutions, but to no avail. For example, for 2019, when I hover over the bottom stack, I want the tooltip to tell me that it represents XX% of uploads for non-partners that year.

 

I've tried the solution below, but the latter part of the argument returns the total number of rows within the table, not the amount for each year.

 

Percentage = DISTINCTCOUNT('Overview'[First_NID])/CALCULATE(DISTINCTCOUNT(Overview[First_NID]),ALLEXCEPT(Overview,Overview[First_Year]))

9 REPLIES 9
daxer
Solution Sage
Solution Sage

I smell a rat here... The rat being a single-table model. Do you happen to have a model with just one big table?

 

So it's using the two tables I've highlighted.

So it's using the two tables I've highlightedSo it's using the two tables I've highlighted

amitchandak
Super User IV
Super User IV

@sbuchan , try a measure like


Percentage = divide(DISTINCTCOUNT('Overview'[First_NID]),CALCULATE(DISTINCTCOUNT(Overview[First_NID]),filter(allselected(Overview),Overview[First_Year]) =max(Overview[First_Year])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks, @amitchandak! However, it's still not working. I receive the following error message - A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

daxer
Solution Sage
Solution Sage

Show the model.

When you say the model, are you referring to the underlying data?

No, I'm referring to the schema.

It's quite large, but the attached screenshot shows the two tables I'm using. CMD Model.PNG

OK, I can tell you straight away you should change the way you model your data. It would take a small book to explain to you why the model is bad. But the fact is: it's very bad. I mean VERY. And I'm not talking about the tables. I'm talking about the relationships. Bi-dir relationships have been thought to solve some very specific issues. Namely, those with many to many relationships that require a bridge table. Nothing else. Second, the *-* (many-to-many) relationships have been designed to solve the issue of granularity and bi-dir filtering should never be enabled on them. If you leave the model as it is right now, very soon---or even now but without being aware---your model will start producting figures nobody will be able to explain and understand. Including you.

 

I'm almost sure this model is ambiguous but the engine does not signalize it.

 

Please do not make the mistakes other have. Change the model according to Best Practices of PBI modeling and dimensional modeling. This advice is all I can do for you right now. Ignore it and you'll be in for troubles later on BIG TIME.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors