cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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.

3 ACCEPTED SOLUTIONS
Super User

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])))`

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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])))`

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
20 REPLIES 20
Super User

Hi,

Share some data.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Hi Ashish_Mathur.

The Data Base has 300 MB 550k rows.

What data do you need?

Thanks

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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:

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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)

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

In Excel First Issue

In excel 2nd Issue:

Super User

Hi,

This formula will resolve your first problem

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur

Yes, i need know the "gran total" and your formula is correct for this, but in addition i put in the question that i need this "grand total" for resume o acumulated qty of customer.

I explain, I need to summarize how many customers buy 12, 11 ..... 1 times for last 12 months.

Like this: (Check this link: https://1drv.ms/x/s!Ar9j2sDrIn_m7Utq8N0TzjZbGesi)

Super User

Hi,

I cannot download that file because it is more than 5 MB in size.  Also, in the image, i see 1 Pivot Table and 2 other tables.  Please tell me exactly the result you want in a new tab and also show the exact figures which you are expecting in that table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur

Ok, (I will filter the last 5 months (201704 to 201708) to reduce the date)

I want see the Qty of Customer buy in the last 5 month. (The max is 5 to 0)

I can reduce the file under 5 MB (only data, without this table)

Super User

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])))`

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur

Very thanks you, you solution is OK !

Now, i have a problems with de Amount, you can help me?.

The other issue i can resolve with the New Column:

Super User

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])))`

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Dear @Ashish_Mathur

I have only 1 problem with your formula:

In the Total Column, only show the value for the "Row 1" and not the real total

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur

Now yes !!!

Very very Thanks you !

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Very very thanks you !!!

Have a good week !