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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DatAlessia
Frequent Visitor

Cumulative distinct count/sum and missing values for years

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:

CustomerYearSellAmount
Customer1201610€
Customer2201750€
Customer3201770€
Customer4201780€
Customer5201720€
Customer6202210€



Now my the matrix visual looks like this:

YearCumulativeCountCumulativeAmount
2016110€
20175230€
20226240€


But it should look like this:

YearCumulativeCountCumulativeAmount
2016110€
20175230€
20185230€
20195230€
20205230€
20215230€
20226240€


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!

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

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:

vyueyunzhmsft_0-1674701431858.png

 

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

 

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1674701431858.png

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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