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.
Hi,
I have a created a page in power bi that shows count of items sold to different customers in a table visual. With that I also have two slicer visuals - One which shows the years and second which shows the months. So now I wanted to calculate average based on the options selected on slicer.
For eg- If I choose Jan, Feb ,March then I want the average count of itmes sold to customer A,B ,C etc in these three months. Whereas, if I select Jan, Feb,March,April then I want the average count of itmes sold to customer A,B ,C etc in these four months. So basically it should take the items sold in each month for each customer and then divide the total by no of months selected (which would be subject to change as it depends on what months we choose).
Is there any way to achieve this? Any help would be appreciated!
Solved! Go to Solution.
Hi @anchal5335,
Assume sample data looks like below:
You can create a measure:
Measure = var countitem=CALCULATE(COUNT('Table1'[Item]),FILTER(ALLSELECTED('Table1'),'Table1'[Customer]=MAX('Table1'[Customer])))
var countmonth=COUNTROWS(FILTERS(Table1[Month]))
return
DIVIDE(countitem,countmonth)
See attached pbix file.
Best Regards,
Qiuyun Yu
Hi @anchal5335,
Assume sample data looks like below:
You can create a measure:
Measure = var countitem=CALCULATE(COUNT('Table1'[Item]),FILTER(ALLSELECTED('Table1'),'Table1'[Customer]=MAX('Table1'[Customer])))
var countmonth=COUNTROWS(FILTERS(Table1[Month]))
return
DIVIDE(countitem,countmonth)
See attached pbix file.
Best Regards,
Qiuyun Yu
Hi @v-qiuyu-msft,
Your solution was helpful but can you please explain me why you took max function? I am still unable to understand that step!
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |