Sum across selected years by keeping only the same individuals with non blank values

[napa]

Hi all,

I want to calculate the sum of 'length' for all countries having non-blank value for each year. When multiple years are selected (e.g. in a slicer), only countries having non-blank values for all the selected years should be included, to assure the consistency of the sample.

Here is the table:

year	country	length
2018	C1	20
2018	C2
2018	C3	80
2019	C1	15
2019	C2	60
2019	C3	85
2020	C1	15
2020	C2	65
2020	C3	75
2021	C1	25
2021	C2	70
2021	C3

The desired result is:

- For 2019: sum(length) = 156

- For 2018-2020 : including only C1 & C3 since C2 is blank in 2018 ->

year	sum(length)
2018	100
2019	100
2020	90

- For 2019-2021 : including only C1 & C2 ->

year	sum(length)
2019	75
2020	80
2021	95

In Excel, I can do this by:

(1) counting the non-blank values for each country over the selected period (eg. 2018-2020) -> x

(2) sum over all countries which have x equals to the number of years selected (3, in this example).

How could I do it in Power BI?

I am quite new to Power BI and DAX so any resources to study is greatly appreciated.

Thank you!

[v-xuxinyi-msft · Accepted solution]

Hi @napa 

Thanks for the reply from @lbendlin .

Maybe you can try the following measure:

sum = 
var _country = CALCULATETABLE(VALUES('Table'[country]), ALLSELECTED('Table'), 'Table'[length] = BLANK())
return 
CALCULATE(SUM('Table'[length]), FILTER('Table', NOT ('Table'[country] in _country)))

Output:

Best Regards,
Yulia Xu

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

[napa]

Thanks a lot, @v-xuxinyi-msft, it does the job perfectly.

[lbendlin]

The process is pretty much the same.

Non Blank Sum = 
var a = filter('Table',[length]<>BLANK())
var b = SUMMARIZE(a,[country],"lg",sum([length]),"ct",DISTINCTCOUNT([year]))
var c = filter(b,[ct]=maxx(b,[ct]))
return sumx(c,[lg])

[napa]

Thanks, @lbendlin, for your help. It's indeed a similar process to Excel.

However, the desired result is the sum of length over all countries (without blank) for each selected year (by the slicer).

I've thus tried something like (after var b, by skipping var c):

return summarize( filter(b,[ct]=maxx(b,[ct])), selectedvalue([year]), "lg_yr",sum([length]))

but without success.

Could you help me out?

Thank you very much