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.
Hey Community,
Looking for a link or a hint to solve following issue:
I need to count the number of customers sorted by discount group and different tresholds of turnover.
F.ex:
Customer discount group A
- # customers with a TO < 1.5K
- # customers with a TO between 1.5K-5K
- # customers with a TO between 5K-15K
- # customers with a TO > 15K
Customer discount group B
- # customers with a TO < 1.5K
- # customers with a TO between 1.5K-5K
- # customers with a TO between 5K-15K
- # customers with a TO > 15K
And so on...
Available (and linked) tables:
- Customers containing discount group code (1 row per customer)
- Order values containing customer No and 1 order value per order row
- Centralised Calendar table
I have tried with and without a turnover measure per customer.
I have been struggling with distinctcounts, countrows, filters and so on but no correct result.
If I use summarize columns with customer # included it seems to work (I get '1' per customer) but as soon as I delete the this column, the totals are not correct anymore.
Thank you for you help or feedback.
David
Solved! Go to Solution.
Hi, @Daro68
Please correct me if I wrongly understood your question.
I tried to create a sample pbix file based on your explanation above.
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
All measures are in the sample pbix file.
https://www.dropbox.com/s/taqnf4wry1efvfk/daro.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Daro68
Please correct me if I wrongly understood your question.
I tried to create a sample pbix file based on your explanation above.
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
All measures are in the sample pbix file.
https://www.dropbox.com/s/taqnf4wry1efvfk/daro.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Dear Kim,
I realized that with my solution I wasn't able to filter the years, so I retried your solution with a clear point of view and your solution is finally the better one.
I am really thankfull to you. The crossfiltering did it... 😄
Kudo's to you Kim !
Kind regards,
David
Hello Kim,
I've been trying till late in the evening to get your formula to work but unfortunately it didn't. I have to admit we have a pretty complex data scheme. Information is retreived from NAVISION SQL. I will try to create a test file I can share.
Hi, @Daro68
Thank you for your message, and it will be helpful for me to look into your sample data to come up with more accurate measures.
Please share your sample file later if it is OK with you.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Dear Kim,
As I would not give up, I finally got it working in the most simplest way I imagined... I just added a new column (Turnover cumulated) to the customer table and from there on I created 5 different treshold measures (Distinctcount, filtered by treshold) and that's all I needed to do. The answer is sometimes so simple.
Anyway, I wish to thank you for your kindness and support and as I said before, you thought me about Crossjoin.
This is what I needed to obtain for this limited customer discount groups.
Have a nice day.
David
Hello Kim,
You understood me correctly. Your idea of working with a non linked table (Turnover Group) looks pretty amazing. Neither have I tried 'Crossjoin'. Let me see if I can insert this in my project. I'll definetely get back to you.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |