Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Distinct Count IF Other Column

Dear Friends I'm Mauricio from Chile, and i have 2 problems with the Power BI.   1. The first quer, we need count the "frequency by month" with the customer (Column AK "Solicitante") buy in our sh...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this

     

    1. Create a one column Table like the one shown
    2. Drag the single column into the row labels of a PowerPivot Table
    3. Write the calculated field formula (measure)
    =COUNTROWS(FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought])))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    You are welcone.  For computing the amount, the formula should be:

     

    =CALCULATE(SUM(Data[Valor Neto USD]),FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought])))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    The formulas should become

     

    =if(HASONEVALUE(times_bought[Times bought]),COUNTROWS(FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought]))),SUMX(SUMMARIZE(times_bought,times_bought[Times bought],"EFGH",COUNTROWS(FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought])))),[EFGH]))

    and 

     

    =if(HASONEVALUE(times_bought[Times bought]),CALCULATE(SUM(Data[Valor Neto USD]),FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought]))),SUMX(SUMMARIZE(times_bought,times_bought[Times bought],"EFGH",CALCULATE(SUM(Data[Valor Neto USD]),FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought])))),[EFGH]))

    Hope this helps.

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Share some data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Ashish_Mathur.

     

    The Data Base has 300 MB 550k rows.

     

    What data do you need?

     

    Thanks

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Just a very small sample and your exepcted result of that sample you share.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        https://drive.google.com/file/d/0B-qoGv5_f_8IX1Jhck5aTWJQbDQ/view?usp=sharing

         

        Dear Friend, this is the link of Data FY17 (Drive)

         

        In PBI i have this:

        The yelow highlighted should count only "1" for month, in the total should count "3" not "21"

        Then, i need count the "qty" of "customers" buy 5 times, 4 times etc.

         

         

         

        This in Excel, is a dynamic table over other dynamic table: