Forum Discussion

NJ23's avatar
NJ23
New Member
6 years ago
Solved

Need help calculation difference between two % of CT Columns

Hi,   I have been struggling with calculating the difference between two values represented as % of column totals to find the % change that works dynamically. Hopefully through these mock tables th...
Need Help
  • omitche2's avatar
    omitche2
    6 years ago

    You need to create measures to make this work dynamically when you drill up or down.

     

    ie

    measure Year 1 Quantity total = calculate(sum(table[Year 1 Quantity ]),ALLSELECTED())

    measure Year 2 Quantity total = calculate(sum(table[Year 2 Quantity ]),ALLSELECTED())

     

    Year 1 % = divide(sum(table[Year 1 Quantity]),[measure Year 1 Quantity total])
    Year 2 % = divide(sum(table[Year 2 Quantity]),[measure Year 2 Quantity total])
     
    % diff = [Year 2 %]-[Year 1 %]
     
     

     

     

omitche2's avatar
omitche2
Frequent Visitor
6 years ago

You need to create measures to make this work dynamically when you drill up or down.

 

ie

measure Year 1 Quantity total = calculate(sum(table[Year 1 Quantity ]),ALLSELECTED())

measure Year 2 Quantity total = calculate(sum(table[Year 2 Quantity ]),ALLSELECTED())

 

Year 1 % = divide(sum(table[Year 1 Quantity]),[measure Year 1 Quantity total])
Year 2 % = divide(sum(table[Year 2 Quantity]),[measure Year 2 Quantity total])
 
% diff = [Year 2 %]-[Year 1 %]