Forum Discussion

jordanesqu's avatar
jordanesqu
Frequent Visitor
6 years ago

Subtotal based on calculated rows

Hello,

I have the below data displayed for 3 consecutive months (NND, NND-1, NND-2).

NND for single period calculates as "Availability"/"Client" and results either 0 or 100.

Subtotal result is equal to positive evaluated clients / all evaluated clients. For instance "NND-2" = 7/8=88.

NND for three periods together calculate the same way with only difference "If at least once in last three periods Availability is positive then result is positive" 

I calculated the "Availabilty 3M" as follows

  • I sum up NND+NND-1+NND-2
  • If the above sum is >=1 than 1

The problem is I do not receive a subtotal for Availability 3M  (in this case 11) but 1 according to sum if function.

Could anyone help to solve that? my expected end result would be:

Availabilty 3M = 11

Client 3M = 12

NND 3M Total =11/12 = 92

 
 
 
 
 

Thanks

Jordan

 

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    jordanesqu , can you share formula

    You have to try something like

    sumx(summarize(Table,Table[Customer_Lv1,Table[Customer], "_1",[Availabilty 3M]),[_1])

    • jordanesqu's avatar
      jordanesqu
      Frequent Visitor
      6 years ago

      Thanks for the tip. I have however more dimensions like region, sales manager, sales rep, material, customer and would like it work flexible depending on selection. in this case sumx did not work for me.