Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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? 

1 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)

Model.jpg

 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
    )
)

 

result.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

Where is the data for the target coming from?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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. 

Can you please post sample data of your tables?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

@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 SalesmenMonth Number of Sales
January12
February 19
YMarch2
ZMarch 13

 

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

Name of SalesmenDepartment
XSoaps
YPerfumes
ZShampoos
AToothpaste
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
Soaps20
Perfume 10
Lotion 5


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

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.

Model.jpg

 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:

matrix.jpg

 

result.jpg

Sample PBIX attached

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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.  

Please provide the corresponding sample data





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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

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)

Model.jpg

 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
    )
)

 

result.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

@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
    )

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors