Need to replace blank with zero but also be able to use a slicer

[Anonymous]

Hi nice people, I have a very specific issue that needs your help. English isn't my first language so please bear with me.

My powerbi report use an Excel file as a source. The Excel file is actually a dump manually downloaded regularly from our company portal. Every salesmen has to report their sales(product no, product type, sales date etc.) on that portal and those details ends up in a very clean excel sheet.

What I want is a power Bi visual , where the rows are names of the salesman. the columns first divided into months so basically 12 columns , each column sub-divided in 'Number of Sales' and 'Sales Target'.

But if a salesman doesn't make a  single sale, their name doesn't end up in the Dump file and as a result not get pulled into PowerBi. To solve this, I have used a second table with the name of all the salesmen our company have and their respective department.and made a Many to One relationship with the dump. So when creating the matrix visual, I pull names of the salesmen from the second table. and the rest of the data/values from dump.

After putting everything in, Salesmen who haven't made a single sale, their 'Number of Sales' value remains blank ( as expected because the dump doesn't have sales data associated with the m). For better aesthetic, I want to put Zero instead of blank.

I have alredy tried couple of solutionsc to replace the blanks with zeros (for example: an if conditon-based measure)  Every one of these works fine., only if I didn't have one additional requirements.

I want to use a slicer which can filter out department. Basically , every salesman has a designated department. After using those measures, the slicer doesn't work as intended anymore. If I select Department A in the slicer, Department B , C, D doesn't go away. Instead salesmen from these department shows up in the visual with their respetive 'Sales Number' being zero. Is there any way I can replace blanks in the visual with zero but at the same time keep my filter option open? 

[PaulDBrown]

Where is the data for the target coming from?

[Anonymous]

Hi @PaulDBrown , thanks for your response. The data for target is coming from a third different table. I didn't mention it becuase I thought it wasn't relevent. My apologies. I am using a lookup value function Dax for this target data. 

[PaulDBrown]

Can you please post sample data of your tables?

[Anonymous]

@PaulDBrown ,  Dump file/excel looks like this (after I use pivoting and group rows and count function to determine the number of sales)  -- 

Name of Salesmen	Month	Number of Sales
X	January	12
X	February	19
Y	March	2
Z	March	13

The 2nd table is for all the salesperson name that we have in the company -

Name of Salesmen	Department
X	Soaps
Y	Perfumes
Z	Shampoos
A	Toothpaste
B	Lotion

Noitce that how Salesmen A and B didn't make a single sale so their info don't end up in dump. Finally the target table - 

Department	Monthly Target
Soaps	20
Perfume	10
Lotion	5

I really appreciate you taking your time here and answering me. Thank you. 

[PaulDBrown]

Thanks for the samples. Here is how I would go about it. First I would set up the model as follows, including a dimension table for month. I created it in Power Query but you can also create it using DAX.

 You can then use the following measures:

Sum Sales = 
COALESCE(SUM('Sales Table'[Sales]), 0)

COALESCE will return the first non-blank value from left to right. So if there are no sales, it returns a 0.

Sum Target =
SUMX (
    VALUES ( 'Month Dimension'[Month] ),
    COALESCE ( SUM ( 'Target Table'[ Monthly Target] ), 0 )
)

Since the target table is unrelated to the Month Dimension table, the measure returns the same value per department for every month (so a monthly target). You need the SUMX iterating over the month field to obtain the total sum for all months.

You then set up the matrix using the Salesperon field from the department table and the Month field from the Month Dimension table & the measures:

Sample PBIX attached

[Anonymous]

Hi @PaulDBrown , I am really sorry that I have took this long to reply. Overwhelmed with work and never had a chance to look into the file. After looking into it, I really appreciate you putting so much efffort behind this. But I still  have this follwoing problem. In your pbix, there is only 1 salesperson per department. But , I have more than 1 in every department. ( 6/7). In that case your relationship breaks down. So I wasn't able to proceed further. Can you kindly provide a solution with each department having more than one salesperson. Thank you.  

[PaulDBrown]

Please provide the corresponding sample data

[Anonymous]

Hi, @PaulDBrown  I have attached a pbix file. In this file I have repeated your approach but for my data ( more than 1 salesperson per department) Look at the target column in the visual. What should I do ? Thank you for your time. 

SampleFIle

[PaulDBrown · Accepted solution]

Thanks for the sample file. I see what you mean.

The model as it stands (I've named the tables to make it easier to follow the calculations in the measure)

 Change the [Sum target] measure to:

Monthly Target =
SUMX (
    VALUES ( 'Month Table'[Month] ),
    COALESCE (
        CALCULATE (
            SUM ( 'Target Table'[Target] ),
            RELATEDTABLE ( 'Salespeople Table' ) // RELATEDTABLE filters table on the one side of the relationship ('Target Table') based on the context created by the table on the many side (salespeople Table).
        ),
        0
    )
)

Sample PBIX file attached

[Anonymous]

@PaulDBrown Works like a charm. I already clicked accept as solution. Thank you so much.
Just a thing that's bugging me, why use a SUMX function in the Monthly target measure? I removed the SUMX part 

SUMX (
    VALUES ( 'Month Table'[Month] ),

  and only kept the following part  and it worked the same. It would be great if you could kindly explain this. Thanks again. 

COALESCE (
        CALCULATE (
            SUM ( 'Target Table'[Target] ),
            RELATEDTABLE ( 'Salespeople Table' ) 
        ),
        0
    )