Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hello @Anonymous 

 

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/

Anonymous
Not applicable

Hey @vivran22, thanks for replying.

Based on your dataset, end result would be the following

Screenshot_20200217-083016_WPS Office.jpg

@Anonymous 

 

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/

@Anonymous 

 

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/

Anonymous
Not applicable

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?

@Anonymous 

 

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/

 

Anonymous
Not applicable

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

@Anonymous 

 

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/

Anonymous
Not applicable

@vivran22 Hmmm...  I don't have access to your dataset in your pbix file, so can't double check.

 

But using the group by solution, you're grouping by order quantity.  I want to group by the number of orders, not by order quantity.  So looking at your data snippet, grouping by order quanitity, would result in 10 different groups (as each row has a different quantity amount), but my desired result would be two groups; one group showing 1 order made with 8 individuals in it, a second group with 2 orders made with 2 individuals in it (Grant Carroll and Skyne Norling).

 

Does that make sense?  If you think your solution is still correct, would you be able to make your dataset available, so I can cross check with your end result please?


Thanks for your help.

@Anonymous 

 

You can find the sample dataset here

 

and the pbix file here

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Anonymous
Not applicable

@vivran22  Thanks for the dataset, that's helpful.

 

So in your dataset, you've grouped by order count, so there are 165 orders with an order quantity of 1.  But that's not what I'm looking for.  I'm looking to provide a chart that shows how many times people have ordered.


So using your dataset, 6 people have ordered once.

Craig Carreira
Dorothy Dickinson
Maureen Grace
Maria Etezadi
Sample Company A
Natalie DeCherney

 

My chart would like the following:

Capture.PNG


Does this clarify it?

Hi @Anonymous ,

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.