Forum Discussion

gmat's avatar
gmat
Icon for Helper I rankHelper I
2 years ago
Solved

Displaying 0s in Summary Matrix Not Working Correctly

I'm currently working on a summary dashboard in PowerBI that displays data from two business units over the last 12 months. I've divided the dashboard into sections, with the first section focusing on one business unit and only showing data for March 2023 and October 2023.

 

The second section is dedicated to the other business unit, displaying data for each month (because it is available).

My challenge is ensuring uniformity in the display, even when there is no data in the top BU . I attempted to insert a new measure to count certain data points and included 0s for cases with no data:

 

 

 Count + 0 = calculate(count(Query1[COUNT]))+0
 
(top BU)

 

However, this approach didn't work as expected. It did pull in display 0s but it interfered with my date filters, pulling in data for the last two years, including January 2023, despite having a relative date filter (in the last 1 year). It is also pulling in all of 2024 months (Jan 24 - Dec 24) with 0s. 

 

I'm puzzled about why it's pulling in all of these other months and would appreciate any help or insights you can provide to resolve this issue. If you need additional details about my setup, feel free to ask.

 
 

 

Need Help

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    To report on things that are not there you need to use disconnected tables and crossjoins.  In your scenario you need a disconnected calendar table for your X axis.

    • gmat's avatar
      gmat
      Icon for Helper I rankHelper I
      2 years ago

      I get the idea here but I am unable to use a disconnected calendar table for my x axis. PowerBI canot use the calendar table without a relationship.

       

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        you can use two calendar tables. One for the data model and one for the X axis.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gmat 

     

    Maybe you can try the COALESCE() function which returns the first expression that does not evaluate to BLANK. If all expressions evaluate to BLANK, BLANK is returned.

    Here is the sample:

    Then add a measure:

    Measure = CALCULATE(COUNTROWS('Table'),'Table'[Name]="DD")

    The result:

    Then change the measure:

    Measure = COALESCE(CALCULATE(COUNTROWS('Table'),'Table'[Name]="DD"),0)

    The result is as follow:

    If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

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