COUNTDISTINCT does not count project if value >1 s present in more than one column (Year 201 - 2021

[T_T00]

Hello Power BI Gurus

I need some help / advice, and hopefully somebody in this group has the time to help me figure this out 😊

I am building a matrix table in Power BI which should show these results:

	2015	2016	2017	2018	2019	2020	2021
Project	23	26	40	42	46	50	47
Locations	96	138	165	189	224	267	295

Sum of 2015	Sum of 2016	Sum of 2017	Sum of 2018	Sum of 2019	Sum of 2020	Sum of 2021
$        16,298,275.99	$        25,319,346.68	$        32,346,011.46	$        37,031,424.24	$        50,369,042.94	$        65,521,030.45	$      103,404,723.18

But I am getting these results, because Power Bi only counts rows where there is a value >0 present in just one column in a row (see highlighted in red below) . I have set "Project and Location Names" to be counted as distinct:

I have a table, similar to this:

Budget #	Project	Location	2015	2016	2017	2018	2019
133	A	cc	$            100,821.92	$              99,178.08	$                             -	$                             -	$                             -
138	A	cc	$                             -	$                             -	$                             -	$              24,769.23	$              24,230.77
139	A	cc	$                             -	$                             -	$                             -	$              20,219.78	$              19,780.22
140	A	cc	$                             -	$                             -	$                             -	$            166,813.19	$            163,186.81
131	A	cc	$                             -	$              24,769.23	$              24,230.77	$                             -	$                             -
918	A	dd	$                             -	$                             -	$              20,000.00	$                             -	$                             -
141	A	ee	$              25,205.48	$              24,794.52	$                             -	$                             -	$                             -
143	A	ff	$            284,821.92	$            280,178.08	$                             -	$                             -	$                             -
144	A	ff	$                             -	$                             -	$                             -	$            298,241.76	$            291,758.24
145	A	ff	$                             -	$                             -	$                             -	$              50,549.45	$              49,450.55
147	A	gg	$            178,958.90	$            176,041.10	$                             -	$                             -	$                             -
901	A	hh	$                             -	$                             -	$              10,000.00	$                             -	$                             -
164	A	ii	$                             -	$              10,109.89	$                9,890.11	$                             -	$                             -

Which I transformed to this by unpivoting Project and Location, and then all Year columns, similar to this:

Attribute	Value	Attribute	Value
Project and Locations	Project and Location names	Year included in Budget	Amount allocated in given Year
Project		2015	$                                                             -
Project		2016	$                                                             -
Project		2017	$                                                             -
Project		2018	$                                                             -
Project		2019	$                                                             -
Project		2020	$                                               78,428.57
Project		2021	$                                               71,571.43
Project		2022	$                                                             -
Locations		2015	$                                                             -
Locations		2016	$                                                             -
Locations		2017	$                                                             -
Locations		2018	$                                                             -
Locations		2019	$                                                             -
Locations		2020	$                                               78,428.57
Locations		2021	$                                               71,571.43
Locations		2022	$                                                             -
Project		2020	$                                                             -
Project		2021	$                                               65,271.82
Project		2022	$                                                             -
Locations		2020	$                                                             -
Locations		2021	$                                               65,271.82
Locations		2022	$                                                             -
Project		2020	$                                                             -
Project		2021	$                                               65,271.82
Project		2022	$                                                             -
Locations		2020	$                                                             -
Locations		2021	$                                               65,271.82
Locations		2022	$                                                             -

Many thanks for any help! 

[lbendlin]

your sample data does not match the expected outcome. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[T_T00 · Accepted solution]

Hi Ibendlin, thank you for responding to my question. It actually resolved itself, the formula wasn't wrong but the data I was using, which is different from what I provided in my question.