Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mybarbie9917
Frequent Visitor

Custom Adding Columns in a Matrix

A. Current Matrix "Sales of Food by Day of Week " 

- Rows: Food dimension 

- Columns: Date dimension  

- Values: [Sales] measure  

mybarbie9917_0-1624656507373.png

 

B. Wanted Result

- Same Rows and [Sales] measure .

- I can turn on the "Total" column by using Subtotal or Grand Total matrix settings.

- Question: I want to add extra "custom" subtotal columns ("Weekdays" and "Weekend" columns in blue background). What should I do to achive custom adding columns in a matrix? 

mybarbie9917_1-1624656607294.png

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

By creating a 2nd day type, weekend/weekday, you can put both into the column part of the matrix and get this result. Will that work?

edhans_0-1624660519122.png

My fields are as follows:

edhans_1-1624660554859.png


See my PBIX file on how I did this. There is a full date table here and I added the Day Type column. If the day is SatSun, then Weekend, else Weekday.

NOTE: I just did this with the June 2021 Power BI desktop, so if you get an error when opening try and click through it, or upgrade.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

By creating a 2nd day type, weekend/weekday, you can put both into the column part of the matrix and get this result. Will that work?

edhans_0-1624660519122.png

My fields are as follows:

edhans_1-1624660554859.png


See my PBIX file on how I did this. There is a full date table here and I added the Day Type column. If the day is SatSun, then Weekend, else Weekday.

NOTE: I just did this with the June 2021 Power BI desktop, so if you get an error when opening try and click through it, or upgrade.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks @edhans for your short workaround, it gives the expected sub-total columns. 

Great! Glad I was able to help @mybarbie9917 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.