cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbrandone
Helper III
Helper III

Calculation Groups, Blank Values, and Slicers

I have a calculation Group that is functioning well except the user asked that the slicers that filter the matrix where the calculation group is held, shows "zeros" if the company did not order any items. I currently have a matrix that shows each month of the year, Year total, and then YoY Change. Like I said, it is functioning well, but I also have 2 slicers that are filtering the matrix. Location (Geographical) and Year. If a company did not order a product for a year, right now it does not show in the Matrix at all for that year. I want to still have it show if they are in our database but just show "0" for the total. 

 

When I go into TE3 and update the calculation group item for "total" to:

 

SelectedMeasure() + 0    (from "SelectedMeasure")

the matrix then shows all the geographical locations even though the slicer is selected on a particular one. All the locations that are not part of the selected geographical location show as "0". It is like the slicers have become non-existent when I do this. 

I tried to select "Show blank values" method and had no luck and then rewrote the measure in the calculation groups a few different ways and they either do not show the zero values or show all companies in the world without filtering location like the slicer shows. 

Any tips on how to get the zeros to show and the slicer to function?

 

I deal with very sensitive data so I do not have a model to share, but the selected measure the group is based off of is just a basic:

 

Calculate(
     Countrows(Orders),
     Table(OrderStatus) = "Completed"
)

The slicers are by Year (From Date table) and by Location (Calculated Column from Contacts table that designate either Local, Domestic or International)

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

I guess you are trying to do show items with no data ... 

 

Say, To show Zeros for countries do not have data for a particular year.

a) Create master list of companies table, which you want to show zeros (aka dim)

        Country = SUMMARIZECOLUMNS(Orders[Country])
 
        Note: I used DAX, you can do this M Query or from the source. If you already have the master data / dim data table, no need to create new one.
 
b) Create relationship to the transaction table (aka fact)
           sevenhills_0-1641497315871.png

 

c) Create new measure, say if you are showing sum of sales as below
         Total Sales Country = IF (Isblank( SUM(Orders[Sales])), 0, SUM(Orders[Sales]))

 

d) Table visual

sevenhills_1-1641497421870.png

 

If this works, do similarly for companies.

 

If it is related to grouping and binning, check this link

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

 

If this is not what you want, Please create some mockup data so that it helps

View solution in original post

1 REPLY 1
sevenhills
Super User
Super User

I guess you are trying to do show items with no data ... 

 

Say, To show Zeros for countries do not have data for a particular year.

a) Create master list of companies table, which you want to show zeros (aka dim)

        Country = SUMMARIZECOLUMNS(Orders[Country])
 
        Note: I used DAX, you can do this M Query or from the source. If you already have the master data / dim data table, no need to create new one.
 
b) Create relationship to the transaction table (aka fact)
           sevenhills_0-1641497315871.png

 

c) Create new measure, say if you are showing sum of sales as below
         Total Sales Country = IF (Isblank( SUM(Orders[Sales])), 0, SUM(Orders[Sales]))

 

d) Table visual

sevenhills_1-1641497421870.png

 

If this works, do similarly for companies.

 

If it is related to grouping and binning, check this link

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

 

If this is not what you want, Please create some mockup data so that it helps

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!