Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
Solved! Go to 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.
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.
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])
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |