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 shop (the customer can buy more that once in the month, but this is only "once" in the frequency, I need know customer buy 0 month in the last "time lapse", 1 month to 12 month in the last year. Example: i need know how much customer buy 12 times for year, 11 times for years until 0 times for year etc.
The data:
In Excel, this is the solution.
Firts: In Rows customer "Solictante" and in Columns "Periodo" Sum(Valor Neto US)
Second: CONTARA the SUM
The Summary:
This is variably month to month
2. The second query is know how much lines of products buy ours customers.
One customer can buy 1 or 3 Lines of Products, (Column EF "AREA") (01_Repuestos; 01_Filtros; 01_Others)
In Excel, the same way that Query 1, in a Rows the Customer and in Columns the "Area", SUM the "Valor Neto USD", and CONTARA
In Power BI i can simulathe this table, but i can't use the "CONTARA" in some MEASURE...
This can filter by Date (Month, Year, Quarter), can filter by Location, etc,
The idea is that this "measure" be variable for this filters.
I hope I have explained in the best way possible.
Thanks for advance
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.
20 Replies
Hi,
Share some data.
Hi,
Just a very small sample and your exepcted result of that sample you share.