Set Analysis Question

[Brysonds]

Hello,

I have data structure like this:

I want to show the sum of 410000 + 410030 to appear only under 410000 (so $215,899.71).

I tried using this set analysis, but it only showed the amount for 410000:

If([GL Account]='410000', Sum({<[GL Account]={'410000'}>} [Sales Revenue]) + Sum({<[GL Account]={'410030'}>} [Sales Revenue]))

How can I get amounts from different GL accounts to appear under other GL accounts (just for the sales revenue column, there are other measures not shown here).

Is this better to perform in the load script vs. set analysis?

[Brysonds · Accepted solution]

I figured out a way to make this work. There may be better ways, but this worked for my purposes:

If([GL Account]='410000', Sum(Total <[Company]> {<[GL Account]={'410000','410030'}>} [Sales Revenue]))

[edwin]

from my perspective, you are really talking about a grouping of GLs.  if you add a new field called GL Group and these two GLs fall under 410000, then it will just be rolled up

[MichelLalancette]

I too believe creating new GL groupings would be easier to maintain in the long run, especially if you think more of these will come up.

In our team, we often use google sheets (we use the G Suite) to create new fields/tables for a better experience than inline tables (although it's on a case-by-case basis).

[Brysonds]

Thanks all for the idea about maintaining a grouping -- that is a good way to handle it and can be done in load script as well, going to explore that design approach since it is easier to manage long term.