ISBLANK() or Measure +0 not working

[GianPaolo]

hello!

I am trying to substitute the blank values I have in this small Matrix with 0 for further calculation

I have written this simple measure based on other answers I have found  but it is still not working

Sum - IsBlankMeasure = IF(ISBLANK(SUM('DB'[conta])) ,0, SUM('DB'[conta]))

I have tried also to add a 0 to the measure but didn't work either:

SUM('DB'[conta]) + 0

Here the link to the file

https://filetransfer.io/data-package/ILykcAZs#link

I would like to have for the row "CCC" and "EEE" the column "DELTA"with (-60) for CCC and (-97) for EEE and the same for the column (%).

Any help will be appreciated

[johnt75]

The problem doesn't appear to be your measure definition but the behaviour of SUMMARIZECOLUMNS, which is what Power BI uses behind the scenes to generate the output for visuals. I created a dummy measure which simply returns 1 and I couldn't get SUMMARIZECOLUMNS to show a value for CCC or EEE when the year was filtered to 2022.

It seems to me like SUMMARIZECOLUMNS isn't even attempting to execute the measure if the underlying category, in this case MODELLO, does not exist in the filtered data.

I think your only chance is to make sure that you have at least 1 entry in each year for each product. You may be able to use Power Query or DAX to work out which products are missing for a given year and create dummy entries for those products

[GianPaolo]

Here is the picture of the Matrix

[PaulDBrown]

You need to create a dimension table for MODELLO and use that in the visual:

[GianPaolo]

Thank you for your answer! This is close to what I need...But the measure (%) is going to 100% for all values...

[PaulDBrown · Accepted solution]

Yes. You need to change the measure to reference the field from the dimension table:

% = 
VAR currentImporto = [Sum - IsBlankMeasure]
VAR AllImporto =
    CALCULATE(
        [Sum - IsBlankMeasure],
        ALL('DModello'[MODELLO])
        )
var ratio =
    DIVIDE(currentImporto,AllImporto)
return
    ratio