Total column using a measure in a matrix to be displayed as grand total and not as subtotals

[KumarC]

Hello, I have started using Power BI literally 3 days back, learning with forums and youtube videos. Please bear with me if this question had already been answered somewhere, I was not able to arrive on the solution after searching for it.

Using a Matrix to display revenue generated, with a filter 'Relative Date' displaying the last 5 days numbers. This matrix will be dynamic with each day data upload and refresh.

What I am trying to achieve is:

Matrix will show the last 5 days individual numbers per row

A Total column displaying the entire revenue so far till that date (basically revenue to date)

I was able to use a measure to get a calculated column and display it in the matrix. But it is actually showing as Sub Totals for each column. I have checked and played around with all the settings but nothing impacted. I am breaking my head with this and it will be really helpful to have this sorted out, since there will be multiple other pages with different filters and categories with similar display functionality.

Current view of the matrix:

Desired result of the matrix:

[PaulDBrown · Accepted solution]

There are a couple of ways  of solving this that I'm aware of. 

1. "Hide" the unwanted measure under each date. To do this you will need to turn off word wrap under column headers in the formatting pane, select the right boundary of each rogue column  and drag it left to hide it. Repeat with each unwanted date  column. The caveat is that need maintenance.

2. Create a custom matrix layout. This involves creating a custom table with the column layout and some DAX. You can see an example in this blog post:

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1... 

[grandtotal · Accepted solution]

well, first you need a Date table which is marked as date table.

you can create one by CALENDAERAUTO() function.

Then you set the relationship:

For the running total you can use this measure:

Measure running total in Date = 
CALCULATE(
    SUM('Table'[amount]),
    FILTER(
        ALLSELECTED('Date'[Date]),
        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
    )
)

Then "hide" your columns as mentioned by @PaulDBrown 

[KumarC]

To be honest, adding this measure was giving me wrong calculation but its on me, I will play around and learn how it works 🙂 And, I am not entirely sure how this Date Table will help me in the future, so parked it aside for the moment and 'circle back after holidays' 😉  Thank you for your input!

[PaulDBrown · Accepted solution]

There are a couple of ways  of solving this that I'm aware of. 

1. "Hide" the unwanted measure under each date. To do this you will need to turn off word wrap under column headers in the formatting pane, select the right boundary of each rogue column  and drag it left to hide it. Repeat with each unwanted date  column. The caveat is that need maintenance.

2. Create a custom matrix layout. This involves creating a custom table with the column layout and some DAX. You can see an example in this blog post:

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1... 

[KumarC]

Hiding the column, its that simple isnt it, smh 😄 thank you very much for the tip. I am continuing to use the measure I created and hide the columns, it worked perfectly! Yes, acknowledging the cutom matrix layout, I will definitely try to learn from the link and have something ready for the long run. Thank you for the solution!

[grandtotal]

can you send us a sample of your data and model?

with a proper data modell you can easily achive this with CALCULATE and time intelligence functions:

https://learn.microsoft.com/en-us/dax/time-intelligence-functions-dax

[KumarC]

https://we.tl/t-clEEOrFa16

sample dataset in csv format attached in this link. i have included only the columns that will be used for the reports, excluded everything else just for saving the file size and its irrelevant as well.

datamodel in this picture. it is a simple dataset with a straightforward pivot like function, except the dataset becomes huge each day without about 200k rows atleast. hence i have started using mysql as database to store data and feed it to powerbi for genering the reports.

hope this helps!