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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
margeausoboti
Frequent Visitor

DAX help

Hello, I am working with a very small data set (see image) and I am trying to find percentages based on filter criteria using DAX.

 

1. My first equation was to find the total count of each animal, per type and color = 161.

2. Next, I need to find the percentage per animal (count of animal divided by the total number of animals).

3. Last, I need to find the percentage per animal type (count of type divided by the total number of animals).

 

I have tried using different methods for this equation, but still am not getting the correct formula. Thank you!

 

 data_set_sample.PNG

1 ACCEPTED SOLUTION

Hi @margeausoboti,

You want to create a calculated column to get the percentage? If it is, please create calculated columns using the fomulas, and check if it works fine.

each type percentage =
CALCULATE ( SUM ( Table[Count] ), ALLEXCEPT ( Table, Table[tipo] ) )
    / SUMX ( ALL ( Table ), Table[Count] )

each Animal percentage =
CALCULATE ( SUM ( Table[Count] ), ALLEXCEPT ( Table, Table[Animal] ) )
    / SUMX ( ALL ( Table ), Table[Count] )

each Color percentage =
CALCULATE ( SUM ( Table[Count] ), ALLEXCEPT ( Table, Table[Color] ) )
    / SUMX ( ALL ( Table ), Table[Count] )

Don't hesitate to ask if you have any other issues.

Best Regards,
Angelia

 

View solution in original post

8 REPLIES 8
Vvelarde
Community Champion
Community Champion

@margeausoboti

 

Hi, Try with this Measures:

 

Count = COUNTROWS(Table1)

Count By ANimal = [Count]/CALCULATE([Count],All(Table1))

 

COunts.png




Lima - Peru

Hi, 

 

Thank you for looking into this for me. This it not the calculation that I am looking for. I want the totals of each line percentage. For example, I want to know the percentage of the amount of amphibians. There are 24 amphibians, and 161 animals total. Therefore, I need the calculation to be 24/161=15%. Does that help? I would need to SUM the type then divide by the total amount. Same for animal total, and color total. Thank you!

Hi @margeausoboti

 

I think Vvelarde has a good answer, there is a difference because of the dataset sample. Check this picture below.

 

c.png

 

 

 

Thank you everyone for all of your help! I went back and checked it again, I had another filter on my chart. After removing the filter, Angelia's syntax is exactly what I needed to resolve the issue. Can anyone recommend a course that I can take to learn DAX? I am a new user of DAX and would liketo become a super user! Thanks!

@margeausoboti

 

A time ago I found a good youtube channel about Power BI, every fridays there is a new topic about DAX. The Curbal woman has become my favourite : ) . Enjoy it !

 

https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw

Thank you!

Hi @margeausoboti,

You want to create a calculated column to get the percentage? If it is, please create calculated columns using the fomulas, and check if it works fine.

each type percentage =
CALCULATE ( SUM ( Table[Count] ), ALLEXCEPT ( Table, Table[tipo] ) )
    / SUMX ( ALL ( Table ), Table[Count] )

each Animal percentage =
CALCULATE ( SUM ( Table[Count] ), ALLEXCEPT ( Table, Table[Animal] ) )
    / SUMX ( ALL ( Table ), Table[Count] )

each Color percentage =
CALCULATE ( SUM ( Table[Count] ), ALLEXCEPT ( Table, Table[Color] ) )
    / SUMX ( ALL ( Table ), Table[Count] )

Don't hesitate to ask if you have any other issues.

Best Regards,
Angelia

 

Hi Angelia,

 

Thank you for your response, you have been very helpful. This isn't however the percentage that I am looking for. I do need a DAX equation. This equation is calculating the row count for each animal, type or color. I also need to take into consideration of the count of animals. So for example, there are 9 frogs, however for each type and color there are different counts (see image). The count at the bottom is showing the number of rows, but I really need the TOTAL of frogs, which is 27. Then 27 divided by the total number of all animals, which is 161. I should get 16.7%. Is this possible to do in DAX? Thanks for all of your help!

 

frogs.JPG

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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