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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aksharacoomar
Helper I
Helper I

Subtotals Issue on Matrix Report

Hi all, 

 

I am facing a unique issue with a report I'm trying to pull up using PowerBI matrix report. I am trying to pull a report which shows the Plan Revenue vs. Actual Revenue and the date hierarchy is as follows: Year, Quarter, Month, Date, Day which is coming in from the Calendar table I created using DAX. I also added additional columns 'Peak vs. Non Peak days' and 'Holidays' from the Plan sheet which is an excel file containing the Plan values and the sales data is pulling in from a database.

 

My issue is that the agrreagated subtotals displayed on the quarterly, monthly level show the 'Day' as Friday and the 'Holidays' and 'Peak vs. Non Peak' field gets populated with a random word in one of the rows whereas they should be blank. I do not know how I need to solve for this as I am fairly new to PowerBI. I also am not being allowed to attach a screenshot here otherwise it is pretty straightforward to understand. Please let me know if you want me to clarify what I just posted cause it is wordy. 

 

Thank you in advance! 

2 ACCEPTED SOLUTIONS

Hello @aksharacoomar , you must condition the value shown in the subtotals, you can use the HASONEVALUE function or the ISINSCOPE function for is, here I leave you a link that can serve you a lot, https://youtu.be/Q_l-6oYOdJ8

View solution in original post

v-robertq-msft
Community Support
Community Support

Hi, @aksharacoomar 

According to your requirement, you can follow my steps:

  1. Create some measures:

 

 

Day1 =

IF(

    HASONEVALUE('Table'[Day]),

    MAX('Table'[Day]),

BLANK())
Holiday1 = 
IF(
    HASONEVALUE('Table'[Holiday]),
    MAX('Table'[Holiday]),
    BLANK())
Peak vs. Non-Peak Days1 = 
IF(
    HASONEVALUE('Table'[Peak vs. Non-Peak Days]),
    MAX('Table'[Peak vs. Non-Peak Days]),
    BLANK())

 

 

For your amount columns, you can use sum(), like this:

 

Amount1 = SUM('Table'[Amount])

 

 

2. Go to the Matrix chart, Use [Day1] to replace [Day], [Holiday1] to replace [Holiday], [Peak vs. Non-Peak Days1] to replace [Peak vs. Non-Peak Days], [Amount1] to replace [Amount], like this:

屏幕截图 2020-10-21 153653.png

And you can get what you want.

 

You can download my test pbix file from here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
Community Support

Hi, @aksharacoomar 

According to your requirement, you can follow my steps:

  1. Create some measures:

 

 

Day1 =

IF(

    HASONEVALUE('Table'[Day]),

    MAX('Table'[Day]),

BLANK())
Holiday1 = 
IF(
    HASONEVALUE('Table'[Holiday]),
    MAX('Table'[Holiday]),
    BLANK())
Peak vs. Non-Peak Days1 = 
IF(
    HASONEVALUE('Table'[Peak vs. Non-Peak Days]),
    MAX('Table'[Peak vs. Non-Peak Days]),
    BLANK())

 

 

For your amount columns, you can use sum(), like this:

 

Amount1 = SUM('Table'[Amount])

 

 

2. Go to the Matrix chart, Use [Day1] to replace [Day], [Holiday1] to replace [Holiday], [Peak vs. Non-Peak Days1] to replace [Peak vs. Non-Peak Days], [Amount1] to replace [Amount], like this:

屏幕截图 2020-10-21 153653.png

And you can get what you want.

 

You can download my test pbix file from here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-robertq-msft , 

 

Thank you, your solution worked 🙂 

Wondering how I can add 'Total monthname' at the end of each month? 

 

aksharacoomar_0-1603288093111.png

 

I also plan on splitting the data by week eg: Oct Wk, Oct Wk2 etc so the Oct month end will be an aggregation of Oct Wk1 - Wk4. If you have any useful documentation or videos that can lead me to what I want to accomplish will also be super helpful. Thank you 🙂 

aksharacoomar
Helper I
Helper I

I was able to add in a screenshot. The Holidays, Peak vs. Non Peak and Day column should not have been populated. 

 

aksharacoomar_1-1603228538801.png

 

Hello @aksharacoomar , you must condition the value shown in the subtotals, you can use the HASONEVALUE function or the ISINSCOPE function for is, here I leave you a link that can serve you a lot, https://youtu.be/Q_l-6oYOdJ8

Thank you! @sortega 

Always, if this solves your doubt you can mark it as your solution

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors