Replace Blank with 0 in Calculate

[potap]

Hi!

Lets say product E doesn't have any transaction in the Sale table. I want it to be displayed with a 0 in my pivot table so I wrote this measure :

=CALCULATE( SUM( factSale[Amount] ) + 0; dimProduct[Group] = "Bike" )

This way, product E gets a 0 but the problem is that every product from every other product group now also get a 0. I don't understand this behavior and I am looking for a solution.

Thank you!

 

[anvg]

Hi
Try with

[COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
    [COLOR=#0070FF]HASONEVALUE[/COLOR][COLOR=#969696] ([/COLOR] dimProduct[Group] [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
        [COLOR=#0070FF]VALUES[/COLOR][COLOR=#969696] ([/COLOR] dimProduct[Group] [COLOR=#969696])[/COLOR] = [COLOR=#D93124]"Bike"[/COLOR];
        [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] factSale[Amount] [COLOR=#969696])[/COLOR] + [COLOR=#EE7F18]0[/COLOR]; dimProduct[Group] = [COLOR=#D93124]"Bike"[/COLOR] [COLOR=#969696])[/COLOR];
        [COLOR=#0070FF]BLANK[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR]
    [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]BLANK[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR]
[COLOR=#969696])[/COLOR]

Regards,

 

[Dan80]

Hi Anvg, interesting stuff again from yourself. Any chance you can explain the formula, if possible please? Why do you need "HASONEVALUE", what does it do? Thanks, Dan

 

[potap]

Hi
Try with

[COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
    [COLOR=#0070FF]HASONEVALUE[/COLOR][COLOR=#969696] ([/COLOR] dimProduct[Group] [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
        [COLOR=#0070FF]VALUES[/COLOR][COLOR=#969696] ([/COLOR] dimProduct[Group] [COLOR=#969696])[/COLOR] = [COLOR=#D93124]"Bike"[/COLOR];
        [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] factSale[Amount] [COLOR=#969696])[/COLOR] + [COLOR=#EE7F18]0[/COLOR]; dimProduct[Group] = [COLOR=#D93124]"Bike"[/COLOR] [COLOR=#969696])[/COLOR];
        [COLOR=#0070FF]BLANK[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR]
    [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]BLANK[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR]
[COLOR=#969696])[/COLOR]

Regards,

Thanks anvg! Your idea got me going

I thought about the IF(VALUES()) part but I was stuck with the "A table of multiple values was supplied where a single value was expected" error. Adding the HASONEVALUE() part is great. I tweaked it a little bit to get the right total.

Total Sales:=SUM( factSale[Amount] )

Total Bike Sales:=CALCULATE(                  
                                   [Total Sales];
                                   dimProduct[Group] = "Bike"
                                   )

Total Bike Sales with 0:=IF(                                  
                                  HASONEVALUE( dimProduct[Group] );
                                  IF(
                                     VALUES( dimProduct[Group]) = "Bike";
                                     [Total Bike Sales] + 0;
                                     BLANK()
                                      );
                                 [Total Bike Sales]
                                 )

I found this blog post which works too IF(COUNTROWS(VALUES())=1) but I think the HASONEVALUE solution is neater.

I was hoping for a simpler measure like the first one I posted What I would like to understand is why Calculate works that way when I add the +0

 

[akice]

Hi Anvg, interesting stuff again from yourself. Any chance you can explain the formula, if possible please? Why do you need "HASONEVALUE", what does it do? Thanks, Dan

The "HASONEVALUE" function call is needed to make sure the dimProduct[Group] column only has one value in the current filter context being evaluated by the inner IF ( VALUES(dimProduct[Group] = "Bike"...) statement. VALUES( dimProduct[Group] ) returns a single column table with all the different values visible in the current filter context. If more than one value was allowed the next/inner comparison operation will fail. And if VALUES( dimProduct[Group] ) does return only a single value, according to the rules of Dax for the VALUES function that single column, single row value is converted into a scalar value and can then be compared to another scalar value ("Bike" in this case). But without the HASONEVALUE to protect the function, you could end up with an error. Make sense?