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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Create pie chart with categories using a measure

Hi,

I have data with customer ids where each row represents transaction info. I am supposed to find average transaction per day for each customer. There is a slicer for date where I can choose last 30 days, or last 7 days. According to the chosen dates, avg per day gets changed. If I just use existing table, there exists an issue of correct average. For example, a customer did 10 transactions in only 5 days among our chosen 30 days. We get average here 10/5 instead of 10/30. The only way to solve this is by creating a measure using another table called calender, having one to many relationship with date of existing table. It solves the problem.

 

Now, I need to make a pie chart with categories like less than 0.5 transaction per day on avg, less than 1, less than 2, more than 5 etc. How can I create such categories using just the measure "avg per day". As we can not make a column in the table as avg per day because the avg values in the table doesnt change with slicer date (as discussed before eg, last 30 days or last 7 days). I could not figure out how to make categories in pie chart using just a measure here. Kindly help me in this!

2 REPLIES 2
Anonymous
Not applicable

Hi! Thanks for your reply. My problem is that, lets say in your example, the table you created will give me same result each time as when I choose different dates in my date slicer (like last 30 days or last 40 days). It can not be placed as a table with fixed values you just showed. This is my measure which say avg transactions per customer.

 

avg per day = DIVIDE(COUNTROWS('existing table'), DATEDIFF(MIN('Calendar'[Date]),MAX('Calendar'[Date]), DAY)+1)
I have a slicer in my dashboard which chooses from Calendar'[Date] and change the values in dashboard for different customers. How can I end up with a pie chart that will categorize less than 2, more than 2, more than 5 etc with changed values each time depending on date slicer?
 
 
ValtteriN
Super User
Super User

Hi,

One way to go about this is to create a SWITCH measure for categorizing. Then you can use this measure as a slicer or create a table containing the categories (if you want them to your pie chart legend).

Here I created a measure for classifying my data. E.g. I want to know more than 300, less than 99 and less than 299. 

Data:

ValtteriN_0-1678606176471.png

 

Dax:

Category_ =
var _sum = SUM(Cakes[Sums])
Var _test =
SWITCH(TRUE(),
_sum>299,"Category 1",
_sum<99,"Category 2",
_sum<299,"Category 3")
 return
_test

End result:
ValtteriN_1-1678606214176.png

 



I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.