Forum Discussion
Helper ICalculate not showing total distinct count
vani wrote:Anonymous I have 6 types of football . Your solution won´t work
It is workable if you arrange your types as 1, 2, 4, 8, 16, 32... but... ya, that's pretty geeky :) Ya... should have seen in your huge FILTER() statement about the 6 types.
Okay, let's talk about why your solution isn't working.
Because you have Clubs on rows, each of those rows is evaluating your expression for just that 1 club. And that is doing what you expect (there is a 2, but no other numbers). [though, it is probably easier to do... a HASONEVALUE('Inscrições'[ID TIPO FUTEBOL]) and VALUES('Inscrições'[ID TIPO FUTEBOL]) = 2) ... maybe filtered for positive types... rather than the big condition you hae now].
Anyway, when you get to the grand total... you are NOT looking at just 1 club, you are looking at all clubs... and if you look at the entire set, it just doesn't match your criteria.
I suspect this is an easy way to resolve your issue for the grand total:
FixedCount := SUMX( VALUES ('Inscrições'[ID CLUB] ); [Disctinct Clubs])
Community Championhi vani
My solution to this was:
DistinctClub = CALCULATE(DISTINCTCOUNT('Futebol-Club'[IDCLUB]);FILTER('Futebol-Club';COUNT('Futebol-Club'[IDCLUB])=1);FILTER('Futebol-Club';'Futebol-Club'[ID TIPO FUTBOL]=2))
GrandTotal = SUMX( VALUES ('Futebol-Club'[IDCLUB]);[DistinctClub])
Medida = If(HASONEVALUE('Futebol-Club'[IDCLUB]);[DistinctClub];[GrandTotal])
