Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Two measures exclude nulls when in a matrix

I have two tables like the following: Loyalty and Rating CRM: DCSS ID DCSS Rep Id DCSS ID + Rep Id Name Loyalty Rating 10001 100 10001+100 Bob A $100,000+ 10002 100 10002+100...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Your measure is correct, it will show blank if there is no value in your ’Loyalty and Rating Data Warehouse‘.

    Click Name column in Value Field in table visual and select show items with no data.

    Now you can see the blank value.

    If you want to show 0 when the value is blank, you can update your measure as [Measure]+0.

    FY20 Actuals = 
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation_Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2020)+0
    FY21 Actuals = 
CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation_Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2021)+0

     Result is as below.

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

Use COALESCE outside your measure:

FY20 Actuals =
COALESCE(CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2020),0)

FY21 Actuals =
COALESCE(CALCULATE(SUM('Loyalty and Rating Data Warehouse'[Donation Amount])
,ALL('Loyalty and Rating CRM'[DCSS ID])
,'Loyalty and Rating Data Warehouse'[Donation_Fiscal_Year] = 2021),0)