Two measures exclude nulls when in a matrix

[Anonymous]

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	Sue	A	$10,000+
10003	101	10003+101	Daniel	F	$100+
10004	105	10004+105	Terry	B	$1000+
10005	108	10005+108	Steve	F	$0+
10006	108	10005+108	Paul	F	$0+

Loyalty and Rating Data Warehouse

DCSS Id	DCSS Rep Id	DCSS ID + Rep Id	Donation_Fiscal_Year	Donation_Amount
10001	100	10001+100	2020	$100,000
10001	100	10001+100	2021	$50,000
10002	100	10002+100	2020	$10,000
10003	101	10003+101	2020	$100
10003	101	10003+101	2021	$10,000
10004	105	10004+105	2020	$1,000
10005	108	10005+108	2021	$100

They are joined on [DCSS ID + Rep Id]. I have measures like the following:

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)

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)

If I have one of the measures, the blank entries are included for that measure. For example, Sue only gave in 2020, if I use the FY21 Actuals, the row for her would have a blank value. When I have both the measures, I lose that row, for example, Paul has no donations but I still want to show his name.

What I want to see is:

	FY21 Actuals	FY20 Actuals
Bob	$50,000	$100,000
Sue	$0	$10,000
Daniel	$10,000	$100
Terry	$0	$1,000
Steve	$100	$0
Paul	$0	$0

What I'm seeing is Paul is missing and blank values where I'd like a $0:

	FY21 Actuals	FY20 Actuals
Bob	$50,000	$100,000
Sue		$10,000
Daniel	$10,000	$100
Terry		$1,000
Steve	$100

Thanks!

[Anonymous · Accepted solution]

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]

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)