Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
napa
Frequent Visitor

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

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
2018C120
2018C2 
2018C380
2019C115
2019C260
2019C385
2020C115
2020C265
2020C375
2021C125
2021C270
2021C3 

 

The desired result is:

- For 2019: sum(length) = 156

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

yearsum(length)
2018100
2019100
202090

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

yearsum(length)
201975
202080
202195

 

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!

1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support

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:

vxuxinyimsft_1-1719562082082.png

vxuxinyimsft_2-1719562150836.png

 

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.

View solution in original post

4 REPLIES 4
v-xuxinyi-msft
Community Support
Community Support

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:

vxuxinyimsft_1-1719562082082.png

vxuxinyimsft_2-1719562150836.png

 

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.

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

lbendlin
Super User
Super User

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])

 

lbendlin_0-1718992231817.png

 

napa
Frequent Visitor

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.