topic Re: Subtotals at a specific row level in DAX Commands and Tips

Subtotals at a specific row level

takui12 — Thu, 26 Sep 2024 11:51:07 GMT

Hello,

I'm trying to figure out this problem all the morning withouth success...basically I've data as shown in the picture, it's an economic statement...

I'd like to insert 2 subtotals in the middle: one after "Costi della Produzione" Total, which should be "Valore della produzione - Costi della produzione" and another before "imposte su reddito dell'esercizio", basically the sum of all bold fields displayed above it. I've tried any kind of trick but nothing... any idea? I'd like it at row level, so they should stay at the same "Macrocategoria" field, and not under them or in a colum....

thank you!

Re: Subtotals at a specific row level

sjoerdvn — Thu, 26 Sep 2024 12:12:26 GMT

You haven't shared any informtion on your model, so that makes it hard to be specific, but the outline of solving is

Add rows for those subtotals to your category dimension
Alter the measure that calculates the amount for a category
Add conditional statements (IF or SWITCH)
Test for context where there is one category value, like HASONEVALUE(dim_category[category])
If the category value equals the custom subtotal, calculate the category, replacing the categories *
for other cases calculate the amount as is

* CALCULATE(SUM(fact[amount]), ALL(dim_category[category]), dim_category[category] IN ('SERVICI', 'COsti per etc', 'etc'))

Re: Subtotals at a specific row level

takui12 — Thu, 26 Sep 2024 13:56:54 GMT

Hi, thank you for your reply. I'm sorry I thought the picture was enough to think about it...well the model is quite simple, it's just a sum of value from a FinalTable in which I've the amoung for any category involved, so it's a simple aggregation of sum per category. The issue came from the needs to create 2 new lines, as stated above, one which is the sum of the first 2 bold categories in the pic (Valore della produzione and costi della produzione, they are profit and loss of operative income baically), that should stay exactly after Costs of production Total, and another which is the sum of all the bold categories less the last one, which should be put before the last bold one... i'm going round and round trying any kind of code but no success till now

if i've to share something else, lemme know

thank you for now