cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

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

View solution in original post

Thank you! @sortega 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.