Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi BI Community Team,
I am counting the number of customer repeat order in excel file and using pivot tablet as the screenshot below.
Source: https://drive.google.com/drive/folders/1EK-1SRY-P3R8AxDQhaqH84qebqnS9kdn?usp=sharing
So any suggestion the measure calculation in Power BI to meet the requirement format below?
Thanks and Regards,
Solved! Go to Solution.
@ADSL OK, PBIX attached below sig. I implemented this with calculated columns but could be done with measures, etc.
Count =
VAR __Customer = [Customer Code]
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Order',[Customer Code] = __Customer),"__InvoiceNo",[InvoiceNo])))
Category =
SWITCH(TRUE(),
[Count] = 1, "1",
[Count] < 4, "2 - 3",
[Count] < 7, "4 - 6",
">7"
)
Category Sort =
SWITCH(TRUE(),
[Count] = 1,1,
[Count] < 4,2,
[Count] < 7,3,
4
)
@ADSL So, are you importing that pivot table into Power BI then? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi @Greg_Deckler,
Sorry to make you are confusing of this topic.
Pivot table that I have mentions, it's refer to doing manual and count not mean importing.
Purpose to ask your support/suggestion if we're doing in Power BI, how to create the measure and calculate it?
Thanks and Regards,
@ADSL So what does your source data look like in Power BI?
Hi @Greg_Deckler ,
Currently I have been created a measure of Total Customer as screenshot below.
For Repeat # of Time Order measure, I don't know how to create it.
You can check the data source in this link - https://drive.google.com/file/d/1Fn9v62evTCEc5y93E5Hw9yGStDgZWrA8/view?usp=sharing
@ADSL Check out the PBIX here for New and Returning Customers: New and Returning Customers - Microsoft Power BI Community
Hi @Greg_Deckler ,
Thank you for your feedback and link.
But It seem not meet the topic purpose that actually we want to know the customer's repeat time order.
Let's say how many times of customer A take order from us in this month and we simplify the time of order to: 1 time, 2 times - 3 times,...etc.
@ADSL OK, PBIX attached below sig. I implemented this with calculated columns but could be done with measures, etc.
Count =
VAR __Customer = [Customer Code]
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Order',[Customer Code] = __Customer),"__InvoiceNo",[InvoiceNo])))
Category =
SWITCH(TRUE(),
[Count] = 1, "1",
[Count] < 4, "2 - 3",
[Count] < 7, "4 - 6",
">7"
)
Category Sort =
SWITCH(TRUE(),
[Count] = 1,1,
[Count] < 4,2,
[Count] < 7,3,
4
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |