Subtotals Issue on Matrix Report

[aksharacoomar]

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! 

[sortega · Accepted solution]

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

[v-robertq-msft · Accepted solution]

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:

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.

[aksharacoomar]

Hi @v-robertq-msft , 

Thank you, your solution worked 🙂 

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

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]

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

[sortega · Accepted solution]

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

[aksharacoomar]

Thank you! @sortega 

[sortega]

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