Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm posting this as similar threads have not managed to answer my question.
We have this calculated measure which assigns a rank to an agent depending on their points, Dax formula is:
Solved! Go to Solution.
I managed to solve my issue, and posting solution for anyone else needing it:
To sum up all the ranks I used the mathematical formula (because math is related to science) for sum of arithmetic progression which is (n/2)*(2a+(n-1)*d) where n is the top number of the progression, a is the first one and d is the difference between each two. To get n I counted the agents that had any pickup points and would therefore figure in the ranking with this:
I managed to solve my issue, and posting solution for anyone else needing it:
To sum up all the ranks I used the mathematical formula (because math is related to science) for sum of arithmetic progression which is (n/2)*(2a+(n-1)*d) where n is the top number of the progression, a is the first one and d is the difference between each two. To get n I counted the agents that had any pickup points and would therefore figure in the ranking with this:
In your case DISTINCTCOUNT will deliver distinct count of CandidateIDs in the context of your hierarchy level (and whatever filter you have applied, of course). The result could be different based on what Date hierarchy level you are at and whether you have candidates that submitted more than one application.
For example, let’s say that Candidate A submitted multiple applications in 2021: 3 in April; 2 in June; and 4 in July (yes, a persistent candidate 😉). For that A candidate your DISTINCTCOUNT measure will return a value of “1” for each of the months that he/she submitted one or more applications (because despite of multiple applications it is still the same/singular/distinct Candidate that month).
And it will also return a value of “1” on the year level (total level). That is because regardless of how many applicants that candidate submitted throughout the year, it is still the same/singular/distinct Candidate.
Your manual Excel total is greater than the Year total you are getting in PowerBI because it looks like in Excel you are just adding up the “month totals”, which is not what you PowerBI measure is doing (you PowerBI measure consistently isolates to distinct values for each hierarchy level).
The DAX DISTINTCOUNT measure will continue to provide the same purpose - counting distinct values - regardless of the level of your hierarchy. The measure's Distinct Count purpose will not change to the "sum" once it’s at the Year-level total - it will continue to be the "distinct" type of calculation.
See below illustrations using print screen from a sample Power Bi page I created to demonstrate your case scenario:
..view collapsed to year level
The value of 1 represents correct distinct count of Candidate for the year (which is the same single “prolific” candidate)
..view by month:
The value of “1” represent correct distinct count of Candidate for each month (same candidate, just multiple monthly applications), and still correct value of 1 on the year level (as it’s still the same single candidate)
…view drill down by day:
Let me know if you have any additional questions. If this answers your question – please mark it as “answer”.
Best,
Vera Carey
I fail to see how this is related to the subject at hand. Could you elaborate on how this would help with the issue of summing up the values of a measure on the same table?
My reply and explanation was meant for another subject (dealing with Distinct count measure). This is a mess up on my part. My bad and my appologies for the confusion.
My reply and explanation was meant for another subject (dealing with Distinct count measure). This is a mess up on my part. My bad and my appologies for the confusion.
@iulian_buhat , try measures like
sumx(filter(values('Agent report'[Agent]), [Order Pickup Reverse] <=6 ), calculate(Sum(Table[Reverse Pickup Rank]) *Sum (Table[Pickup points)/ sum(Table[ reverse pickup rank])))
Sum(Table[Reverse Pickup Rank]) is not recognized as valid as it will not let a measure be the subject. Eliminating the sum parts to make it a valid expression just gives it blank value for most. I fail to see exactly how it helps as the initial filter seems to just take the bottom 6 for which it would calculate reverse rank / (Points* rank) , which... if my math holds out well... just gives you the pickup points.
And yep, bottom 6 have exactly their pickup points
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |