DAX Group Total missing one group

DAX DAX Calculations
1

Hi
Our accounting system has an account code of five digits (specified as a text column).

I am creating a summary P & L account which we call a Group Ten Summary e.g. account codes between 10000 to 10009 are added together under group 1000.

I have attached the pbix file and if you look at the Summary P/L & B/S tab you will see the summary. Every group total work absolutely fine except the first Income group 0000.

If you look at the second tab (Profit & Loss) you will see the three income codes that comprise the group 0000 (00001, 00002, 00003) are there.

The group summary calculation is performed by a measure “Total Group Ten” in the Profit and Loss Measures section.

I am at a complete loss to understand why this is not working.

Any help greatly appreciated.

Best regards

Paul

Accounts.pbix (1.2 MB)

2

.
Hi Paul,

What in your opinion is the actual problem? I don’t have a clear picture of what you’re after…
The results for the measure seem to be adding up - so please advise.

Thanks!

2020 result

image

2019 result

image

3

Hi,

Thank you for your reply.

f you look at the first tab, the group 0000 is completely missing. It should contain value of 122,484.12, it being the total of the first three items in you image above.

Group Ten Summary

Best regards

Paul

4

Hi Paul,

Hope you’ve made a small typo :wink:

image
.
Just one slight change was enough, here’s the updated measure:

Total Group Ten = 
CALCULATE(
    SUM( NlTransactionsPosted[Amount] ),
    FILTER( NominalCode,
        NominalCode[ NominalGroupTenCode] IN VALUES( NominalCode[ NominalGroupTenCode] )
    )
)

I hope this was helpful.

5

Hi Melissa

Thank you so much for the solution. I really appreciate it.

I am getting much better at DAX thanks to Enterprise DNA and you have put another small brick in the wall of my knowledge.

Best regards

Paul

6

Glad I could help Paul, always a pleasure.

We all walk the same road to a town called ‘DAX mastery’ just keep following the trail, you’ll get there! :cowboy_hat_face:

7

Hi again

Could I be really cheeky and ask a supplemental question.

What I would like to be able to do is to have the Group Ten display but have a facility to click on an individual line and have it expand to all the account codes in that group.

I previously worked with another BI system where this was standard but seems a little more difficult in Power BI.

Best regards

Paul

8

Hi Paul,

Do you mean something like this:
image

If so I changed the Table into a Matrix visual

  • added the NominalCodes
  • toggled off Stepped layout
  • enabled subtotal per row and toggled it off for NominalCode

By up- and downdrilling you can switch between the levels.
Accounts.pbix (1.2 MB)

1 Like
9

Exactly what I was looking for.

Thank you again.

Best regards

Paul

10

It’s great to know that the issue has been fixed @PaulBoyes. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!