cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular 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 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!

1 ACCEPTED SOLUTION
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:

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.

4 REPLIES 4
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:

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.

Regular Visitor

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

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

Regular 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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.