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
iulian_buhat
Frequent Visitor

Sum of a measure

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:

Order Pickup Reverse = Rankx(ALL('Agent report'[Agent]),'Agent report'[Points Total Pickup],,asc,dense)-1
 
I want to make a sum of all ranks, so if there are six agents, add numbers from 1 to 6 and get 15, if there are 53 get 1431.... so on

HOWEVER, this would then be used as there is another measure I want to summarize, which is calculated from other measures, which is calculated from other measures

Here's the file 
 
Basically, I want to summarize Pickup points then define a new measure Share points = Reverse Pickup Rank * (Sum of Pickup points/sum of reverse pickup rank)

Yes, this would basically split the total into many shares, and depending on their order from the bottom, last guy gets one share, next gets two, so on.
 
Thank you for the assist.
1 ACCEPTED SOLUTION
iulian_buhat
Frequent Visitor

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:

 

COUNTROWS(FILTER(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup]<>0))
 
With countrows doing the actual count, filter making sure they have pickup points and allselected making sure they are the one currently filtered by slicers. So, final formula for sum of arithmetic progression looks like this:

Order Total = (COUNTROWS(FILTER(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup]<>0))/2)*(2+(COUNTROWS(FILTER(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup]<>0))-1))

For the total sum of pickup points I used sumx, with allselected once again to make sure it's the ones being shown, and sumx evaluating the expression for each row:

Absolute total Pickup Points = sumx(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup])

View solution in original post

7 REPLIES 7
iulian_buhat
Frequent Visitor

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:

 

COUNTROWS(FILTER(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup]<>0))
 
With countrows doing the actual count, filter making sure they have pickup points and allselected making sure they are the one currently filtered by slicers. So, final formula for sum of arithmetic progression looks like this:

Order Total = (COUNTROWS(FILTER(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup]<>0))/2)*(2+(COUNTROWS(FILTER(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup]<>0))-1))

For the total sum of pickup points I used sumx, with allselected once again to make sure it's the ones being shown, and sumx evaluating the expression for each row:

Absolute total Pickup Points = sumx(ALLSELECTED('Agent report'[Agent]),'Agent report'[Points Total Pickup])
Vera_Carey
Frequent Visitor

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

Vera_Carey_0-1642792852642.png

 

The value of 1 represents correct distinct count of Candidate for the year (which is the same single “prolific” candidate)

..view by month:

Vera_Carey_1-1642792852643.png

 

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:

Vera_Carey_2-1642792852646.png

 

 

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. 

amitchandak
Super User
Super User

@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

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.