Calculation Groups, Blank Values, and Slicers

[dbrandone]

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)

[TaraCrane]

I have the same issue and the solution doesn't work. Regardless where i add the 0, the filter stops working. I tried it in the original measure and in the editor measure.

my editor formula is meant to filter the page on 3 months based on filter selection (if selection is January then it shows January, February and March).  this works fine until I try and add 0, and then the filter stops working and shows all years and months.

 VAR NumofMonths = 3
 VAR ReferenceDate = MIN('Date Table 1'[Date])
 VAR NextDate = 
    DATESINPERIOD(
        'Date Table 2'[Date],
        ReferenceDate,
        NumofMonths,
        MONTH
    )
VAR Result = 
    CALCULATE(
        IF(ISBLANK(SELECTEDMEASURE()),BLANK(),SELECTEDMEASURE()),
        REMOVEFILTERS('Date Table 1'),
        KEEPFILTERS(NextDate),
        USERELATIONSHIP('Date Table 1'[Date],'Date Table 2'[Date])
    )
 RETURN
    Result

 Any ideas?

[sevenhills · Accepted solution]

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)

           

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

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