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 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      sir it can not fullfill my condition can we chat one to one this is my mail id 
      [email protected]

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

        Hi, Anonymous 

        I'm sorry, I may not fully understand your needs,MY UNDERSTANDING IS TO GROUP BY YEAR AND MONTH [SHIP TO PARTNER. PARTNER CODE], FILTER FOR [SHIP TO PARTNER. PARTNER CODE], and then return distinct count of [SHIP TO PARTNER. PARTNER CODE].

        Also, for policy reasons, I can't have other one-on-one conversations with you.

        For your question, can you describe your needs in detail and provide your expected outcome values? To make the question clearer and simpler?

        If your need is urgent and you need one-on-one communication,

        If you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

        It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

        The link of Power BI Support: https://powerbi.microsoft.com/en-us/support/

        For how to create a support ticket, please refer to https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

         

        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