Forum Discussion
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 | 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:
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!
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)+0FY21 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)+0Result 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.
2 Replies
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)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)+0FY21 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)+0Result 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.