Count Rows based on dynamic column value

[Anonymous]

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]))

[Anonymous]

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

[Anonymous]

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

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

[amitchandak]

@Anonymous , 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])))

[Anonymous]

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.

[Anonymous]

Show the model.

[Anonymous]

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

[Anonymous]

No, I'm referring to the schema.

[Anonymous]

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

[Anonymous]

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.