cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cchow
Frequent Visitor

Creating a visual based on a calculated group

Hello,

I have a dataset of customers and orders. I'd like to count how many orders each costumer has (this is dependant on the time frame end user has chosen) and then display a column chart of number of orders by amount. Eg, along x axis, 1 order, 2 orders, 3 orders. Y axis number of customers.

Could someone help please?
12 REPLIES 12
vivran22 Solution Sage
Solution Sage

Re: Creating a visual based on a calculated group

Hello @cchow 

 

This is what I have tried to recreate your scenario:

 

Sample Dataset:

Capture.PNG

 

Visuals by customer & product:

Capture2.PNG

 

Is this what you are looking for? 

 

If not, then please share more details on the requirement.

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

cchow
Frequent Visitor

Re: Creating a visual based on a calculated group

Hey @vivran22, thanks for replying.

Based on your dataset, end result would be the following

Screenshot_20200217-083016_WPS Office.jpg

Community Support
Community Support

Re: Creating a visual based on a calculated group

Hi @cchow ,

Can you please share a dummy file? Or please share some sample data and more details like relationships among many tables and among the tables and the columns. We will understand clearly and solve it quickly.

How to Get Your Question Answered Quickly

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vivran22 Solution Sage
Solution Sage

Re: Creating a visual based on a calculated group

@cchow 

 

Are you trying to create a frequency distribution chart (something like a histogram)?

 

 

In this case, this is what I have come up with:

 

Table =
SUMMARIZE (
    dtSales,
    dtSales[Order Quantity],
    "Customer Count", COUNTA ( dtSales[Customer Name] )
)

 

Visual:

 

Capture3.PNG

 

pbix file

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

vivran22 Solution Sage
Solution Sage

Re: Creating a visual based on a calculated group

@cchow 

 

Did I answer your query?

 

Please confirm.

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

cchow
Frequent Visitor

Re: Creating a visual based on a calculated group

Hi @vivran22 ,

 

Apologies for slow reply, I wasn't in work yesterday.

 

The calculated table looks good, as does the chart.  I was looking for order quantity along the x axis and customer count along the y axis, so I can make that change.

 

The only remaining issue is that this represents the full dataset.  My report has a date slicer that I would like this table/chart to change depending on what date period they choose, eg week, month, quarter, year. 

 

Is this possible?

vivran22 Solution Sage
Solution Sage

Re: Creating a visual based on a calculated group

@cchow 

 

In this case, I would recommend using the "Group By" option in Power Query by taking the reference of the parent table:

 

4.0.png

 

 

4.PNG

 

Keep the date column from the parent table

 

 

You can then create & filter your visuals accordingly and any changes in the parent table will be reflected here:

 

5.PNG

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

 

cchow
Frequent Visitor

Re: Creating a visual based on a calculated group

@vivran22 

 

The solution you've provided works to a certain extent, but not in the way that solves my issue.

 

As you can see from my column chart in my second post above, I want to group by the number of orders made during set time period.  So using your example, Grant Carroll and Skye Norling have two orders each (regardless of quantity ordered), the rest has had one order.  

 

So the bar chart would show 8 customers ordering once and 2 customers ordering twice.

 

The groups would have to be calulcated on the fly (dependant on date slicer) and be used to categorise the number of customers in each order count group.

vivran22 Solution Sage
Solution Sage

Re: Creating a visual based on a calculated group

@cchow 

 

I am not sure if I have understood your explanation correctly. 

 

The current solution(with Power Query Group By) is also giving the exact same results. The only difference is that now we have included a dimension of date with it:

 

6.PNG

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors