topic Re: Set Analysis Question in App Development

Set Analysis Question

Brysonds — Thu, 09 Mar 2023 15:46:28 GMT

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?

Re: Set Analysis Question

Brysonds — Thu, 09 Mar 2023 16:04:55 GMT

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]))

Re: Set Analysis Question

edwin — Thu, 09 Mar 2023 16:30:48 GMT

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

Re: Set Analysis Question

MichelLalancette — Thu, 09 Mar 2023 18:06:12 GMT

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).

Re: Set Analysis Question

Brysonds — Thu, 09 Mar 2023 18:08:22 GMT

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.