Displaying Proper Grand Total

Has anyone found a way to make the grand total subtract two values instead of adding them together? See below. I am assuming if I somehow found a way to make all of my expenses negative it might work but that would create other problems in the calculations that derive what is revenue versus an expense.

I have Grand Total turned off for now since it doesnt show the values that I want.

Profitbase has a wonderful visual that allows this to be done, the problem is I have too many values for their visual and since it takes a month between getting visuals approved (versions) they are not able to accommodate my need as quick, officially. This means their visual right now is unstable for my use case.

@jmwdba,

This is a really interesting problem. I suspect the answer will follow the general pattern of creating a virtual table, and then testing through the IF(HASONEVALUE()) construct whether the row is a total or not, similar to the approach used below. However, the subtotal issue throws a new wrinkle into the pattern that I suspect can be handled through the ISINSCOPE() function to determine whether it’s a grand total or a subtotal.

If you can please post your PBIX file, I’d be eager to take a shot at a solution.

Thanks.

  • Brian

Yikes, so I would essentially do this for every Grant Total Column that I need? What I setup was just to create measures for each which is what you see up at the top with the Red arrow. But I would like it if it were a part of the table itself.

I am studying the PBIX from the link you shared.

@jmwdba,

Yes, I think you would have to do a separate measure for each column of the table. However, if you build these using a measure branching approach, once you get the first one to work correctly, the rest should come together quickly via simple search and replace.

  • Brian

Its amazing that something so basic and essential to accounting and finance is not directly supported in Power BI without some workaround. Profitbase comes the closest with their custom visual but it has some kinks that need to be worked out for people with larger data display needs.

@jmwdba,

I agree. The good news is that with the aggressive update cycle that Microsoft has implemented for Power BI it wouldn’t surprise me if that gets remedied soon. A similar situation – a number of months ago, I needed to conditionally format card visuals and had to use a custom visual (Cards with States) to do that. Then one recent update cycle all that functionality was included in Power BI. With all the buzz the ProfitBase visual has generated, it wouldn’t be surprising to see that functionality incorporated soon.

In the meantime, I did find this DAX general pattern for dealing with matrix subtotals and grand totals, which you might find helpful.

  • Brian
1 Like

Hi @jmwdba, A response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!