cancel
Showing results for
Did you mean:
Highlighted
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.

12 REPLIES 12
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:

Visuals by customer & product:

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/

Frequent Visitor

## Re: Creating a visual based on a calculated group

Based on your dataset, end result would be the following

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.

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.

Solution Sage

## Re: Creating a visual based on a calculated group

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:

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/

Solution Sage

## Re: Creating a visual based on a calculated group

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

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?

Solution Sage

## Re: Creating a visual based on a calculated group

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

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:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Frequent Visitor

## Re: Creating a visual based on a calculated group

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.

Solution Sage

## Re: Creating a visual based on a calculated group

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:

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Announcements

#### 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!

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

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 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

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

Top Solution Authors
Top Kudoed Authors