Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Active 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 t...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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"))



     

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi , Anonymous 

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

"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's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago


      " i already used this measure  here it is this is my pbi file please help me its urgent  in this file sold to partner code is my dealer and invoice quantity is their sales and remaining thig is" previous.
      Sir ,if you can please response quickly its really urgent

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , Anonymous 

        Thanks for your quick response!

        I check the .pbix file, sorry i use the ALLSELCETED() function, so it will be filtered by the slicer.

        You cana try to use :

        Count = var _t = ALL('CUBE_INVOICE')
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 ,"code", [SHIP TO PARTNER.PARTNER CODE])))

        The result is as follows:

        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