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,

 

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dear Ashish, thanks you.

       

      This is how you say:

       

      But i need this "Medida" in de "Rows". 

      I need count how much "Solicitante" buy 12 times for year, 11 times, etc. The acumulated QTY of Customer.

       

      Thanks you.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        I am not clear.  Your first question was that how do you get the distinctcount in the Grand Total column.  My formula above solved that problem.  See screenshot below.

         

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Try these formulas please. They both can be filtered by other fields. Check this file: https://1drv.ms/u/s!ArTqPk2pu-BkgSAPrPbBryA8GXHy

     

    Measure 2 =
DISTINCTCOUNT ( Tabla1[Periodo] )
    Measure 3 =
DISTINCTCOUNT ( Tabla1[Area] )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dear v-jiascu-msft.

      Very thanks you.

       

      But same as the previous answer, i need too Count:
      First issue: how much customer buy 12 times for last 12 month, 11 times for last 12 month, etc.
      Second issue: how much customer buy 3, 2 and 1 type of "Area" (Repuestos, Filtros and Others)

       

      Please check this file:

      https://1drv.ms/x/s!Ar9j2sDrIn_m7Utq8N0TzjZbGesi

       

      In Excel First Issue


       

      In excel 2nd Issue: