Set Analysis % Calculation

[Anonymous]

Below is my calculation to get the amount of money by pay type for a range.

Sum({<[Amt Paid] ={">0<2500"}>}[Amt Paid])

What I would like to do is now calculate the percent on that range so check would be about 54/56%, I thought it was as simple as " / sum([Amt Paid])" however that just takes the over all total? not just the summed section.  Do i need to write another analysis set to capture and divide the total?

Thank you in advance for any assistance anyone can provide

[sunny_talwar · Accepted solution]

May be this

Sum({<[Amt Paid] ={">0<2500"}>}[Amt Paid])/Sum(TOTAL {<[Amt Paid] ={">0<2500"}>}[Amt Paid])

[Anonymous]

So I did need to do another set analysis to capture the total separately, thank you.

[sunny_talwar]

If the ranges are predefined, I would create them in the script and then instead of creating them as multiple expressions, I would just create them as one expression with 2 dimensions.

[Anonymous]

the ranged are unfortunately not predefined, i set the ranges in the expression.  Should I still build the calculation in the data load script as a best practice?

[sunny_talwar]

But you are hard-coding these ranges in your expressions, right? or are they decided based on some input from your users?

[Anonymous]

Correct, I am hard coding them.

[sunny_talwar]

So, you can hard code them in the script then (assuming Amt Paid will always be a positive number, you can do like this)

If([Amt Paid] <= 2500, Dual('Transactions of $2,500 or less', 1),

    If([Amt Paid] <= 10000, Dual('Transactions $2,501 to $10,000', 2),

          If([Amt Paid] <= 100000, Dual('Transactions $10,001 to $100,000', 3),

              ....

))) as Transaction_Range

and then in your chart

Dimensions

Payment Method

Transaction_Range

Expression

Sum([Amt Paid])/Sum(TOTAL <Transaction_Range> [Amt Paid])

[Anonymous]

I didnt think of that, thank you, I will give it a go.

Many Thank You