Forum Discussion
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...
Hi,
Try this
- Create a one column Table like the one shown
- Drag the single column into the row labels of a PowerPivot Table
- Write the calculated field formula (measure)
=COUNTROWS(FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought])))
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])))
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.
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
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: