Divide 2 values based on different filters/aggregates

[kdc02]

Hi,

Im trying to create a visualization wherein it will take the number of IDs and divide it by the total items (by using count distinct). For example:

	Number of IDs	Total items	Percentage
Category A	200	300	67%
Category B	100	300	33%

The problem is that when I try to divide them using the DIVIDE(DISTINCTCOUNT('table'[ID]), DISTINCTCOUNT('table'[items])), it just filters the number of items something like this:

	Number of IDs	Total items	Percentage
Category A	200	120	1.67%
Category B	100	60	1.67%

Is there a way to create a calculation that will get the total items despite the Number of IDs having filters? I will be using a slicer to change the numbers per month as well.

Thank you.

[Anonymous · Accepted solution]

Hi @kdc02 ,

According to your sharing sample, here are my test process

To achieve your goal, you can follow these steps:

Here are my test data

1.Create a measure by using DAX

Number of IDs = COUNT('Table'[ID])
total of items = COUNTROWS(ALLSELECTED('Table'))
Percentage = DIVIDE([Number of IDs],[total of items])

2.Create a Slicer by using month

3.Final output

I hope my solution can help you, but if it doesn’t, don’t worry. You can always share your thoughts and feedback with me by posting a comment below this post.

In order for you to solve the problem faster, you can refer to the following documentation

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,

Albert He 

[Arul]

@kdc02 ,

Can you share some raw data? Also, do you consider filters apllied in the slicer for the calculation or not?