Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
kdc02
Frequent Visitor

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 IDsTotal itemsPercentage
Category A20030067%
Category B100

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 IDsTotal itemsPercentage
Category A2001201.67%
Category B100601.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.

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1701682300022.png

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

vheqmsft_1-1701682516262.png

3.Final output

vheqmsft_2-1701682575560.png

vheqmsft_3-1701682617967.png

 

 

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 

View solution in original post

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1701682300022.png

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

vheqmsft_1-1701682516262.png

3.Final output

vheqmsft_2-1701682575560.png

vheqmsft_3-1701682617967.png

 

 

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
Super User
Super User

@kdc02 ,

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.