Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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:

image.png

 

 

 

In Excel, this is the solution.

Firts: In Rows customer "Solictante" and in Columns "Periodo" Sum(Valor Neto US)

Second: CONTARA the SUM

 

image.png

 

The Summary:image.png

 

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)

image.png

 

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

 

image.png

 

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.

image.png

 

 

I hope I have explained in the best way possible.

 

Thanks for advance

3 ACCEPTED SOLUTIONS

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

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

20 REPLIES 20
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish_Mathur.

 

The Data Base has 300 MB 550k rows.

 

What data do you need?

 

Thanks

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

 

image.png

 

 

This in Excel, is a dynamic table over other dynamic table:

 

image.png

 

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

Distinct Count IF Other Column.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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)

 

Please check this file:

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

 

In Excel First Issue


image.png

 

In excel 2nd Issue:

 

image.png

 

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
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Dear Ashish, thanks you.

 

This is how you say:

image.png

 

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.

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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)

 

image.png

 

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
https://www.linkedin.com/in/excelenthusiasts/
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)

Link: https://1drv.ms/x/s!Ar9j2sDrIn_m7U03_WvdoQpKGnqk

 

image.png

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

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

Very thanks you, you solution is OK !

 

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

 

image.png

 

The other issue i can resolve with the New Column:

 

Profundidad Nacional = CALCULATE(DISTINCTCOUNT(TablaBI[Area]);FILTER(TablaBI;TablaBI[Solicitante]=EARLIER(TablaBI[Solicitante])))

 

image.png

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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

 

image.png

 

 

In your example:

 

image.png

 

 

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.

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

Now yes !!!

 

Very very Thanks you !

image.png

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Very very thanks you !!!

 

Have a good week !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.