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:

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        This formula will resolve your first problem

         

        =if(HASONEFILTER(Tabla1[Periodo]),SUM([Valor Neto USD]),DISTINCTCOUNT(Tabla1[Periodo]))

        Hope this helps.