Forum Discussion
Helper IIActive dealer count
Dear community,
" I want to calculate active dealers count but don't know how satisfied with this condition active dealers are-
Active dealer:- Who was appear in more than 3 months in a year is the active dealer. it means the dealer has a sale for more than 3 months in a year no matter in one month how many times it's a sale. it has sale more than 3 months in a year c
I have this type of data and also many column like year, month no etc."
table name - cube invoice
| date | month | dealer | sales |
| 1-1-22 | jan | a,b,c | 10,20,30 |
| 1-2-22 | feb | a,b c,d, | 10,20,30,40, |
| 1-3-22 | march | a,v,b,c,d,e | 10,20,14,02,12 |
| 1-4-22 | apr | d,e,e,a | 10,20,30,82 |
| 1-5-22 | may | ||
| 1-6-22 | june | ||
| 1-7-22 | july | ||
| 1-8-22 | aug | ||
| 1-9-22 | sep |
as we see "a" is apper more than 3 months in year so it is a active dealer so there is active dealer count is 1
now i want this on dax measure
please help
Thank you sir for your all valuable suggestions this is done by this measure
Active Customers = VAR SummaryTable = ADDCOLUMNS ( SUMMARIZE ( CUBE_INVOICE, CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE]), "@num months", COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( CUBE_INVOICE, Calendar_new[YM] ) ) ) ) RETURN COUNTROWS ( FILTER ( SummaryTable, [@num months] >= 3 ) )This measure working when we selecct more than 3 month on calender slicer but with this measure we done it for ficial year
Active dealer in ficial year = CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))
12 Replies
- amitchandak
Super User
SachinNamdeo-20 , I am hoping the dealer and sales are not comma separated. they are in rows. Use a date table, but for distinct count use month year from sales table
You can create a measures like
YTD = CALCULATE(Distinctcount(sales[Month Year]) ,DATESYTD('Date'[Date],"12/31"))
GT 3= countx(filter(Values(Sales[Dealer]) , [YTD] >3), [Dealer])
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
SachinNamdeo-20"Sir actually they are they are comma seprated it means dealer a has sales 10 and dealr b with 20 sales and so on ,and one thing also sales is a column of cube_invioce so i can not use it like your measure with month year .
these measure gives me value but not not accurate please help sir"
- v-yueyunzh-msft
Community Support
Hi , SachinNamdeo-20
According to your description, you want to calculate the count of active users who have more than 3 sales in different month.
For your table , it not a good data structure to dispose the data.
Here are the steps you can refer to :
(1)This is my test data:(2)We need to convert the table in Power Query Editor, You can put the M code in "Advanced Editor" to refer to :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3LCYBADATQXnIeMIl7sJfFQ/whgiIerMdarMy4oisJmcsbEiNJIYWyKoEmW87D09Cg9RSGMkqmGpE0u6FvXoXu5xAeWmY629aOCe8PR/8VJIAVcm+qhVyzdfPr2Mf+Hyqn9QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, month = _t, dealer = _t, sales = _t]), Custom1 = Table.TransformColumns(Source,{{"dealer",(x)=>Text.Split(x,",")},{"sales",(x)=>Text.Split(x,",")}}), #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Table.FromColumns({[dealer],[sales]},{"dealer","sales"})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"dealer", "sales"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"dealer", "sales"}, {"dealer", "sales"}) in #"Expanded Custom"The result table is like this:
(3)Then we can apply the data to Desktop and then we can click "New column" to add a [Year_month] column in our table as a dimension:
Year_month = year([Date]) * 100 + MONTH([Date])(4)Then we can create a measure like this:
Count = var _t = ALLSELECTED('Table') var _t2 =SUMMARIZE(_t ,[Year_month],[dealer]) var _t3 =FILTER(ADDCOLUMNS(_t2 ,"count" ,var _dealer = [dealer] return COUNTROWS(FILTER(_t2 ,[dealer]=_dealer))) , [count]>3) return COUNTROWS(DISTINCT(SELECTCOLUMNS(_t3, "dealer" ,[dealer])))Then 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
- SachinNamdeo-20
Thank you sir for your all valuable suggestions this is done by this measure
Active Customers = VAR SummaryTable = ADDCOLUMNS ( SUMMARIZE ( CUBE_INVOICE, CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE]), "@num months", COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( CUBE_INVOICE, Calendar_new[YM] ) ) ) ) RETURN COUNTROWS ( FILTER ( SummaryTable, [@num months] >= 3 ) )This measure working when we selecct more than 3 month on calender slicer but with this measure we done it for ficial year
Active dealer in ficial year = CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))
- SachinNamdeo-20
"I also have this type of data you describe in m codes but your measure gives me blank value plese guide me now what to do This is my datatable ss here ship to partner code is my dealer and have their sales are also combine in next frame please help."
- v-yueyunzh-msft
Hi , SachinNamdeo-20
Thanks for your quick response!Do you mean you have had the data structure like this:
If this , you need to add a calculated column in this table:
Year_month = year([D DATE.FULLDATE]) * 100 + MONTH([D DATE.FULLDATE])After it , then we create this measure:
Count = var _t = ALLSELECTED('Table') var _t2 =SUMMARIZE(_t ,[Year_month],[SHIP TO PARTNER.PARTNER CODE]) var _t3 =FILTER(ADDCOLUMNS(_t2 ,"count" ,var _code= [SHIP TO PARTNER.PARTNER CODE] return COUNTROWS(FILTER(_t2 ,[SHIP TO PARTNER.PARTNER CODE]=_code))) , [count]>3) return COUNTROWS(DISTINCT(SELECTCOLUMNS(_t3, "SHIP TO PARTNER.PARTNER CODE" ,[SHIP TO PARTNER.PARTNER CODE])))If this still dose not work , can you share the same data structure sample data like yours to us (without sensitive data) and the end result value you want to ?
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
