Distinct Count based on calculated percentage measure

[emily-wise]

I have a table with hundreds of thousands of rows for individual sales, but each sale is designated to a specific group. I have a slicer that selects the desired reporting period (end date) and then I can calculate the total sales for the rolling-12 months based on the selected date both for the national total and each group. Finally, I have created a measure that shows the percent contribution to the nation for each group during the selected time period (Group Sales %). This all works great. What I'm struggling with is getting a simple distinct count of the number of groups whose percent contribution (Group Sales %) is > 1%. See the screenshot below which shows the list of group sales percents, and the distinct count measure I've written. You can see that it's counting each group once, without being filtered by the measure. How can I change this measure to correctly count ONLY the groups where Group Sales % >= .01? The resulting card should show 19, not 92 (which is the count of ALL groups).

[emily-wise · Accepted solution]

Thank you both! Due to this being filtered on a date slicer, I ended up having to force calculated columns for each month and then creating a measure based on the selected month and related percent. 

[Anonymous]

Hi @emily-wise ,

@Jihwan_Kim  provided a good solution. I have another method here, I hope it can be helpful to you.

1% Groups Count =
CALCULATE(
DISTINCTCOUNT('Sales Jan 23 to Present'[Dealer Group]),
FILTER(
SUMMARIZE('Sales Jan 23 to Present', 'Sales Jan 23 to Present'[Dealer Group], "GroupSalesPercent", [Group Sales %]),
[GroupSalesPercent] >= 0.01
)
)

SUMMARIZE Returns a summary table of the requested totals across a set of groups, creating a table containing each dealer group and its corresponding table. For detailed information, please refer to the document: SUMMARIZE function (DAX) - DAX | Microsoft Learn.

If the problem still exists, please provide a pbix file with representative data so that we can better help you solve the problem.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it suits your requirement.

1% Groups Count: =
COUNTROWS (
    FILTER (
        VALUES ( 'Sales Jan 23 to Present'[Dealer Group] ),
        [Group Sales %] >= 0.01
    )
)