Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I need a hand for a cumulative count which returns blank for missing values in a matrix visual.
I have a table for which I have to count the customer who bought something THAT year in THAT region.
The measure I have for the cumulative count is this:
Cumulative Count =
VAR MinYear = MIN(Table[YearSell])
VAR Calcola = CALCULATE(DISTINCTCOUNTNOBLANK(Table[Customer]),Table[YearSell]<=MinYear)
RETURN Calcola
and this is for the cumulative sum:
Cumulative Amount =
VAR MinYear = MIN(Table[YearSell])
VAR Calcola = CALCULATE(SUM(Table[Amount]),Table[YearSell]<=MinYear)
RETURN Calcola
This works except for the year/region for whose I have no sold items.
So if my data is like this:
Customer | YearSell | Amount |
Customer1 | 2016 | 10€ |
Customer2 | 2017 | 50€ |
Customer3 | 2017 | 70€ |
Customer4 | 2017 | 80€ |
Customer5 | 2017 | 20€ |
Customer6 | 2022 | 10€ |
Now my the matrix visual looks like this:
Year | CumulativeCount | CumulativeAmount |
2016 | 1 | 10€ |
2017 | 5 | 230€ |
2022 | 6 | 240€ |
But it should look like this:
Year | CumulativeCount | CumulativeAmount |
2016 | 1 | 10€ |
2017 | 5 | 230€ |
2018 | 5 | 230€ |
2019 | 5 | 230€ |
2020 | 5 | 230€ |
2021 | 5 | 230€ |
2022 | 6 | 240€ |
So basically filling the blank years with values for the year before, then continuing the cumulative count.
I know the problem is that for some years(2018-2021) I altogether do not have a value, so this is why the measure does not work.
I created another table containing ALL the years and created the relationship, but now I do not know how to write the correct measure.
Does someone know how?
Thanks in advance to everyone!
Solved! Go to Solution.
Hi , @DatAlessia
Thanks for your sample data you provided. According to your description, you want to "Cumulative distinct count/sum and missing values for years".
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We can click "New Table" and enter to create a dimension table:
Year = GENERATESERIES( MIN('Table'[YearSell]) ,MAX('Table'[YearSell]))
(3)Then we can create two measures:
CumulativeCount = var _cur_year = MAX('Year'[Value])
var _t = FILTER(ALLSELECTED('Table') ,'Table'[YearSell]<= _cur_year)
return
COUNTROWS(_t)
CumulativeAmount = var _cur_year = MAX('Year'[Value])
var _t = FILTER(ALLSELECTED('Table') ,'Table'[YearSell]<= _cur_year)
return
SUMX(_t,[Amount])
(4)Then we can put the fields we need on the visual and we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
You may download my PBI file from here.
Hope this helps.
Hi , @DatAlessia
Thanks for your sample data you provided. According to your description, you want to "Cumulative distinct count/sum and missing values for years".
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We can click "New Table" and enter to create a dimension table:
Year = GENERATESERIES( MIN('Table'[YearSell]) ,MAX('Table'[YearSell]))
(3)Then we can create two measures:
CumulativeCount = var _cur_year = MAX('Year'[Value])
var _t = FILTER(ALLSELECTED('Table') ,'Table'[YearSell]<= _cur_year)
return
COUNTROWS(_t)
CumulativeAmount = var _cur_year = MAX('Year'[Value])
var _t = FILTER(ALLSELECTED('Table') ,'Table'[YearSell]<= _cur_year)
return
SUMX(_t,[Amount])
(4)Then we can put the fields we need on the visual and we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |