How to calculate percentage of total without using percentageOfTotal?

Question & Answer
, ,
1

Here is my table:

image
image1311×563 58.6 KB

and here is my Lasy 7 Days Sales expression:

max(ifelse(CANCELLED = 'N' AND SHIFTDATE >= addDateTime(-7,'DD',${pDate}) AND SHIFTDATE <= ${pDate},{ORDER_WITHOUT_TAX_AMOUNT},0), [Branch,{ORDER_NUMBER},SHIFTDATE]) )

My data is Invoice Headers joined with Invoice line items table. so the invoice/order totals are being repeated by how many line items there are after the join. this is why I’m taking the Max over Branch, Order_number, and ShiftDate as the unique invoice identifiers.

My question is how can I calculate percentage of total without using the built-in function percentOfTotal() and is there a way to make a measure only appear under “Total” and not under every column in the dimension value?

Here is what I was trying to achieve, this was done using percentageOfTotal():

image
image1806×539 53.6 KB

2

Hi @Ali_B,
To get percentage of total without using the built in function, you could do something like:

sumOver({Last 7 Days Sales}, [{Region}, {Shift Date}], PRE_AGG)/sumOver({Last 7 Days}, [{Shift Date}], PRE_AGG)

Let me know if you have any additional questions or if this works for what you’re trying to achieve!

3

This gives the following error:

image
image1015×168 3.92 KB

4

Hi @Ali_B,
You’re nesting in a LAC-A function within a LAC-W. If we alter the calculation you sent above, we should be able to remove the errors.

maxOver(ifelse(CANCELLED = 'N' AND SHIFTDATE >= addDateTime(-7,'DD',${pDate}) AND SHIFTDATE <= ${pDate},{ORDER_WITHOUT_TAX_AMOUNT},0), [Branch,{ORDER_NUMBER},SHIFTDATE], PRE_AGG)

5

Thank you for the hint,

The expression you gave did not return the correct sum. This one did:

maxOver(ifelse(CANCELLED = 'N' AND SHIFTDATE > addDateTime(-7,'DD',${pDate}) AND SHIFTDATE <= ${pDate},{ORDER_WITHOUT_TAX_AMOUNT},0), [Branch,{ORDER_NUMBER},SHIFTDATE], PRE_AGG)
/
countOver(
{ORDER_WITHOUT_TAX_AMOUNT},
[Branch,{ORDER_NUMBER},SHIFTDATE],
PRE_AGG
)

And finally, your percent to total expression works now.
Thanks again.

1 Like
6

Glad it was able to assist!