Forum Discussion

margeausoboti's avatar
margeausoboti
Advocate I
8 years ago
Solved

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!

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

     

8 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    margeausoboti

     

    Hi, Try with this Measures:

     

    Count = COUNTROWS(Table1)

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

     

    • margeausoboti's avatar
      margeausoboti
      Advocate I
      8 years ago

      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!

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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