In matrix 0 instead of empty cell

[Erburi92]

Hello,

I want to have 0 in matrix insteand of empty place. I have tried several functions, however, it failed. Please see the matrix table:

I tries several functions:

Blanks =

IF (

    CALCULATE (

        SUM(FraudSite[Total Fraud Total])

            = 0.00

    ),

    0,

    SUM(FraudSite[Total Fraud Total]

and

No blank Total Fraud =

VAR _total_faud = SUM(FraudSite[Total Fraud Total])

RETURN

IF(ISBLANK(_total_faud),0,_total_faud)

and

Sum = sum(FraudSite[Total Fraud Total])+0

Could you help me to solve this issue?

Column parameters:

Thank you:)

[TomMartens]

Hey @Erburi92 ,

this measure:

SalesAmount Zero instead of blank = 
var valueSalesAmount = CALCULATE( SUM( 'FactOnlineSales'[SalesAmount] ) )
return
IF( ISBLANK( valueSalesAmount )
    , 0
    , valueSalesAmount
)

return a 0 instead of blank value as you can see in the below picture:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Erburi92]

Thank you, however, it does not work 😞 no zeros added

[TomMartens]

Hey @Erburi92 ,

create a pbix file that contains sample data, upload the pbix to onedrive, google drive, or dropbox and share the link.

Regards,

Tom

[Erburi92]

https://easyupload.io/h711c2

Thank you:)

[Erburi92]

@TomMartens maybe you found a solution?

[TomMartens]

Hey @Erburi92 ,

it seems I have to install additional software to get the pbix file.

I will not do this.

Regards,

Tom

[Erburi92]

@TomMartens ,

Please see link below:

https://www.dropbox.com/s/fon8izivqw8n062/example.pbix?dl=0

I hope this works

[Erburi92]

@TomMartens ,

Please see link below:

https://www.dropbox.com/s/fon8izivqw8n062/example.pbix?dl=0

I hope this works

[TomMartens · Accepted solution]

Hey @Erburi92 ,

the problem you are facing is based on the auto-exist behavior, read a detailed explanation here: Understanding DAX Auto-Exist - SQLBI

To overcome this problem you have to create a star schema creating dimension tables for the site and the month, then create relationships between the dimension tables and your FraudSite table (the fact table). Here you will find an introduction to data modeling with Power BI: Design a data model in Power BI  - Learn | Microsoft Docs

You have to be aware that you also need to fill the blank values for the site in the FraudSite, e.g., not applicable. The value also has to be present in the dimension table.

Hopefully, this helps to tackle your challenge.

Regards,
Tom