Forum Discussion
Sum of Distinct Count
I have sales data that is organized by weeks, but unfortunately, these weeks do not align with the calendar weeks. Each week, a specific set of customers are scheduled to be visited and invoiced in six different locations. The customers who are invoiced are considered productive, and unique customer codes are divided by the cumulative scheduled customers to determine the productive percentage. Depending on the criteria, a journey plan may consist of four or five weeks.
Once the plan enters its second week, I need to plot the sum of the distinct count of customer codes for each week. However, this is not happening, and I am unsure of what I am missing. I have utilized the below measures.
Customers Tapped = DISTINCTCOUNT(Sales[Customer Code])
Customers Scheduled = SUM(PJP[PJP])
Productive (%) = DIVIDE([Customers Tapped], [Customers Scheduled])
If I use the measure Customers Tapped, I would get 7,516 as an answer. As a way around this, I created another measure (see below), but the result is still the same.
Sum of Distinct Counts =
SUMX(
VALUES(Sales[JC]),
CALCULATE( DISTINCTCOUNT(Sales[Customer Code]) )
)
The actual answer is 12,376.
I am seeking assistance in identifying what I may be missing in my calculations.
3 Replies
- Greg_Deckler
Community Champion
atifakram First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Thank you for your suggestion, Greg_Deckler!
However, I am currently using journey plans as a filter, and the manual calculation method is not producing consistent results.
I believe a dynamic solution would be the most effective way to address this issue.
- Greg_Deckler
atifakram There's no reason you can't respect the filters by using something like ALLSELECTED bu the key is that you need to SUMMARIZE the information in a table VAR the same way it is summarized in the matrix/table. So, you have 2 levels of summarization in your matrix. I have no clue what the column names are but you have the first level 14, 15 and then the second level which looks like some text description so the measure should be something along the lines of:
Measure Total = VAR __MeasureRow = [Current Measure] //this is the measure that returns the correct information by row VAR __CurrentCount = COUNTROWS('Table') VAR __AllCount = COUNTROWS(ALLSELECTED('Table') VAR __Table = SUMMARIZE(ALLSELECTED('Table'), [First Column], [Second Column], "__Value", [Current Measure]) VAR __Result = IF(__CurrentCount = __AllCount, SUMX(__Table,[__Value]), __MeasureRow) RETURN __Result
