Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have created a calculated table to show unique customers (customer mobile number), their unique visits (number of unique invoices), and a group based on their number of visits.
I have also created a separate calculated table to show unique invoices (unique bill numbers), Bill Value ($ value of the invoice), and a group to define the bill range (by $ value of the bill)
The problem I face:
Upon filtering the data, the count/sum of unique invoice don't seem to match (they match when the calendar filter is removed) as sown below. How can I ensure that both the numbers match when I filter the data for a particular time period + bill value range + # visits. In the sample below I have filtered data to show the differences I see.
Here is the dummy file.
Solved! Go to Solution.
Hi @AmeenVanakar.,
Here is my solution.
Proud to be a Super User!
Hi @v-xingshen-msft & @_AAndrade
Many thanks for helping with my issue so far.
Unfortunately, this still does not solve the problem.
I tried to replicate the expected outcome in Excel & found anomalies. Here is a scenario
1. Customer 334434424566 has 3 unique bills between 1st Sep 2024 to 30th Sept 2024. however, these 3 bills are all on the same day & not different days. Hence the visit filter should show "1 Visit" but instead it's showing "3 visits"
2. During these 3 visits, the customer had different bill values matching different "bill range" criteria. Hence when we filter for visits + bill range the numbers don't show accurate display of the data.
Solution required:
1. Is there a way to group these 3 bills from customer 334434424566 & show as a sum within the same date?
2. The sum of bill numbers should be dynamic & change according to the date period & visits range selected.
3. Visit range should combine all bills within the same day as one & show as "1 visit" as on that day. This should also be dynamic & show a sum of all visits when a larger time period is selcted & link to visits filter.
Here is the link to excel file & the updated .pbix report shared by @_AAndrade .
Excel File Customer Billing Data_workings.xlsx
.pbix file Analysis_DinRange.pbix
Hi @AmeenVanakar,
I did some changes on my first pbix file and I think now is working as you want. For example, on 04/05/2023 we have 3 rows without customer number so the old dax formula will count 3 visits but with the new one is counting only one visit.
Take a look at my new pbix file.
Proud to be a Super User!
Hi @_AAndrade ,
The calculations work very well with the measures you've created 👍. However, we need to bring this all together in a column format along with bill range (bill value) so we can use it as a filter at the dashboard level, only then will we be able to run the analysis accurately.
Is there a way to use the measures as filters?
I didn't understand your point.
What is wrong on my pbix file? I have one filter with visits and have your first filter with bill classification.
What is not working properly?
Proud to be a Super User!
Can you help me with the bill range filter? the current filter doesn't filter the 'Total Sales Final' values within the specified range.
Hi @AmeenVanakar.,
Here is my solution.
Proud to be a Super User!
Hi @AmeenVanakar,
The problem is in your Customer Mobile Number Table, because you are using one table to classify the customer by the number of visits of all data.
For example, Customer 3165296135 had 2 unique invoices, so 2 visits classification but he had one Invoice on 28/06/2024 and other on 18/08/2024.
In your example you are using filters to "2 Visits" and one period between 01/08/2024 and 23/09/2024. Therefore, all your measures are rigth. The client had 2 visits (all time) and in the select period he had only one invoice.
What you want to have in the final output? The classification must be done depending also the selected period?
Proud to be a Super User!
Hi @_AAndrade
Thanks for your update.
Customer 3165296135 should be excluded in this case under the column "Sum of Unique Invoices" as he/she had only 1 visit between 8/1/2024 to 9/23/2024. Is there a way to do this?
Hi @AmeenVanakar,
I'm attaching a pbix file with my solution.
Any doubt please let me know.
Proud to be a Super User!
Hi ALL
Firstly _AAndrade thank you for you solution!
And @AmeenVanakar ,As you said When Customer 3165296135 has only one visit in the selected date, we exclude him, that is, we show blank value, you can use the following DAX, which can help you to exclude Customer IDs that have only one visit record, I hope it will be helpful for you!
Sum of Unique Invoices =
CALCULATE(
DISTINCTCOUNT(Customer_Data[BILL NO]),
FILTER(
VALUES(Customer_Data[CUSTOMER MOBILE NO]),
CALCULATE(
DISTINCTCOUNT(Customer_Data[BILL NO]) > 1
)
)
)
If you have further questions, you can contact me at any time, I will reply to you at the first time after I receive the message, looking forward to your reply!
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |