If we select date in slicer display sales of the products that have null display as zero

[avulasandeep]

Hi every one,

If we select date in a slicer display the products that does have sales show the null sales as zero.

Here is link for previous post please reply it's urgent .
https://community.powerbi.com/t5/Desktop/if-isfiltered-then-display-sales-for-null-values-also/m-p/7...

Thanks
Sandeep

[TomMartens]

Hey,

I would create two measures the first only aggregates just the sales like so:
Total Sales = SUM('<name of the table>'[name of the column])

And a second measure that checks if the measure is blank like so:
Total Sales with zero =
var maybeblank = [Total Sales]
return
IF(ISBLANK(maybeblank) , 0 , maybeblank)

Hopefully this is what you are looking for.

Regards,
Tom

[avulasandeep]

Hi @Anonymous ,

Here I need to select month slicer ..if I select month from the slicer ....then the products of the sales having null has to display as zero along with products of the sales not having nulls or zero for that selected month.
Please refer my question or post from above

Thanks
Sandeep

[TomMartens]

Hey,

please prepare a pbix file that contains sample data and reflects your data model, upload the pbix to onedrive or sharepoint and share the link.

Regards,

Tom

[avulasandeep]

Hi
@TomMartens ,

Here I pbix file for reference .

https://drive.google.com/file/d/11ZXT9WolIMF78k7oy3FlATQ50mwFm6yF/view?usp=drivesdk

Thanks
Sandeep

[avulasandeep]

Hi @Anonymous ,
Can you please check I have updated the link for downloading pbix file.

Thanks
Sandeep

[TomMartens · Accepted solution]

Hey,

I understand now.

You should consider to create a star schema, a one table solution has always its shortcomings

First I created a table Dimension Product using this DAX statement:

dimension product = DISTINCT('Sheet1'[Products])

Then I created a relationship between your data table (the fact table) and the new dimension product table:

I created a measure (it is more convenient) to reference the measure then to always write the complete formula:

Total Value = 
SUM('Sheet1'[Value])

Then I write the final measure:

Value showing zeros = 
var theValue = [Total Value]
return
IF(ISBLANK(theValue) , 0 , theValue)

This allows to create the following table:

Please be aware that the Products are coming from the Table "Dimension Product" and not from the data table. This is a best practice as well as data modeling using a star schema.

Regards,

Tom

[avulasandeep]

Superrrrr.....thanks

It's working fine. Thanks once again


Thanks
Sandeep

[TomMartens]

Hey,

the link you provided requires a login, I'm not going to login to download your sample data. Maybe you can provide another link or update the settings of that link.

Regards,

Tom