Forum Discussion

SachinNamdeo-20's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
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...
  • SachinNamdeo-20's avatar
    SachinNamdeo-20
    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"))



     

SachinNamdeo-20's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
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 , 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

  • SachinNamdeo-20's avatar
    SachinNamdeo-20
    Icon for Helper II rankHelper II
    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 , SachinNamdeo-20 

      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