Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to Solution.
Hi,
Try this
=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,
Share some data.
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.
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:
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:
Hi,
This formula will resolve your first problem
=if(HASONEFILTER(Tabla1[Periodo]),SUM([Valor Neto USD]),DISTINCTCOUNT(Tabla1[Periodo]))
Hope this helps.
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.
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.
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)
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.
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
Hi,
Try this
=COUNTROWS(FILTER(SUMMARIZE(Data,Data[Solicitante],"ABCD",DISTINCTCOUNT(Data[Periodo])),[ABCD]=MIN(times_bought[Times bought])))
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:
Profundidad Nacional = CALCULATE(DISTINCTCOUNT(TablaBI[Area]);FILTER(TablaBI;TablaBI[Solicitante]=EARLIER(TablaBI[Solicitante])))
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])))
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
In your example:
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.
You are welcome.
Very very thanks you !!!
Have a good week !
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |