Forum Discussion
Divide 2 values based on different filters/aggregates
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.
Hi Anonymous ,
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
2 Replies
Anonymous ,
Can you share some raw data? Also, do you consider filters apllied in the slicer for the calculation or not?
Hi Anonymous ,
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
