Forum Discussion

LickNewin's avatar
LickNewin
Regular Visitor
2 years ago
Solved

Subtotal Divided by Row Value

I'm trying to create a measure which takes the value from each Row, and divides it by the Subtotal to give the Percentage of the Subtotal per row. 

 

I'm currently using this measure, but it just gives a value of 100% for each row, which is wrong.

Resignation Percentage = DIVIDE(COUNT(JoinedDataTable[Reason Code]),CALCULATE(COUNTROWS(JoinedDataTable)))

 

The below is what it should look like:

As an example, the '18-25' Age Bracket, should calculate as 22 / 204 = 0.1078 (10.78%)

Age BracketTotal%
Junior10.49
18-252210.78
26-30188.82
31-3573.43
36-4062.94
41-4583.92
46-5083.92
51-5573.43
56-6062.94
61-6583.92
66-70157.35
71-75209.80
76-802411.76
81-852713.24
86-90188.82
91-9552.45
96-9910.49
100+31.47
Total204100
Need Help
  • Uzi2019's avatar
    Uzi2019
    2 years ago

    Hi LickNewin 

    Just small change in formula
    Try this 

    Measure 2= 
    var Total= CALCULATE(COUNT(Table[Reason code]),ALLEXCEPT(Table, Table[Reason code])

    RETURN
    Divide(Measure1,Total)
     

     

    It would give you expected output!
     
    I hope i answered your question!
     

7 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    hi LickNewin 
    Try below dax for measure

     

    Measure 1= Count ( Table[Reason code])

    Measure 2= 
    var Total= CALCULATE(COUNT(Table[Reason code]),ALL(Table))

    RETURN
    Divide(Measure1,Total)
     
    you can see my output

     

     

    I hope I answered your question!

     

     


    • LickNewin's avatar
      LickNewin
      Regular Visitor
      2 years ago

      Hey Uzi,

      Unfortunately that doesn't work how I wanted.

      It works fine when I have all the Reason codes selected in the Filter, but as soon as I filter it, the values are wrong.

       

       

    • Uzi2019's avatar
      Uzi2019
      Community Champion
      2 years ago

      Hi LickNewin 

      Just small change in formula
      Try this 

      Measure 2= 
      var Total= CALCULATE(COUNT(Table[Reason code]),ALLEXCEPT(Table, Table[Reason code])

      RETURN
      Divide(Measure1,Total)
       

       

      It would give you expected output!
       
      I hope i answered your question!
       
      • LickNewin's avatar
        LickNewin
        Regular Visitor
        2 years ago

        Hey Uzi,

        It's coming up with an error that says the Syntax for RETURN is incorrect