Sum Over doubt


I’m looking for creating a pivot table that is calculating for each month and equipment the total money spent.

My dataset has the columns “po_number”, “po_line_number”, “received_amount”, “month_received” and “equipment” (among others we don’t care now).

One po_number can have multiple po_line_number, and each po_line_number can be repeated multiple times, and for those cases, the received_amount, month_received and equipment will be repeated as well. So actually, the received_amount for a po_line_number, is the unique value that is repeated. Below one example:

po_number po_line_number received_amount month_received equipment
456123 1 100 € May Refrigeration
456123 1 100 € May Refrigeration
456123 2 450 € June HVAC
333123 1 900 € May Refrigeration
775123 1 600 € August Conveyor

From above example, actually the amount received in May for Refrigeration should be 1000€ (Only 100€ from 456123, and 900€ from 333123):

May June July August
Refrigeration 1000€
HVAC 450€
Conveyor 600€

How would I be able to achieve this, baring in mind that I don’t want to display on my pivot neither po_number nor the po_line_number?

Thanks for the support!

@galaugus ,

have a look at this post as level aware calculations are useful for different scenarios. ( Level-Aware Calculations: Tips and Best Practices )

1 Like

Thanks for the quick reply. Exactly what I was looking for.

One further question. I have created my LAC calculated fields (3 in total) and based on a parameter, I’m choosing which one I want to be displayed. Everything is working apart from when wanting to sort my pivot based on this parameter, I’m receiving the following message:

“The Level Aware Calculation (LAC) aggregate expressions is not supported in this calculation at this moment. Please remove the LAC aggregation expression from metrics.”

Any idea what could be the bug?


Hi @galaugus,

Can you use LAC-W instead of LAC-A for this? If you want to use LAC-A in your table, you can keep your calculated fields as is but for sorting create another version of the calculated using LAC-W.

1 Like

Hey David, thanks for the suggestion. The thing is that I would need to use a LAC-W(LAC-A) nested function, since first need to calculate the money spent per po_line_number (using a LAC-A function; sum(max(received_amount, [{po_line_number}]), and from this calculation should use the LAC-W function, to calculate this number per category, correct? And as you said in your article (very well explained btw, thanks!) it is not possible.

Hi @galaugus,

I’m glad you found the article helpful.

If you want to sort, I think you’ll have to use LAC-W instead of LAC-A in your calculated field. LAC-W(LAC-A) isn’t allowed.

Try this calculated field:
received_mount / countOver(po_number, [po_number, po_line_number], PRE_AGG),
[equipment, month_received],

I’m basically counting how many times each instance of po_number appears in every combination of po_number and po_line_number, and dividing by that number to effectively eliminate the duplicates. It’s not as pretty as LAC-A but I think it should work.