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 all,
I've spent hours trying to solve this seemingly simple problem and reading previous solutions, which normally works, but this time I just can't find the right answer.
I have one table containing a daily percentage for a number of categories:
date | category | daily percentage |
01-Jan | A | 76% |
02-Jan | A | 84% |
03-Jan | A | 72% |
01-Jan | B | 23% |
02-Jan | B | 52% |
03-Jan | B | 45% |
01-Jan | C | 50% |
02-Jan | C | 82% |
03-Jan | C | 75% |
I want to create a new table that summarises the average of this percentage, per category
category | summary percentage |
A | 77.33% |
B | 40% |
C | 69% |
I've tried a few different solutions but the one below is where I am currently:
summary table =
SUMMARIZE(
SELECTCOLUMNS('table 1',
"category",
'table 1'[category],
"summary_percentage",
AVERAGE('table 1'[daily percentage])),
[category],[summary_percentage]
)
What returns is the average percentage of the total [daily percentage], ignoring the category:
category | percentage |
A | 62.11% |
B | 62.11% |
C | 62.11% |
I also created a 'Many to one (*:1)' relationship (Single direction) between the new table and my previous table for [category].
Any help would be greatly appreciated! Thank you
Solved! Go to Solution.
@DavidJ , Create a measure and use that in visual
Measure = average(Table[daily percentage])
Or new table
table =
Summarize(Table, Table[Category], "Percent", average(Table[daily percentage]) )
@DavidJ , Create a measure and use that in visual
Measure = average(Table[daily percentage])
Or new table
table =
Summarize(Table, Table[Category], "Percent", average(Table[daily percentage]) )
Hi @amitchandak - thank you so much! I was just overcomplicating it.
The below worked perfectly. I didn't realise I didn't have to use the SELECTCOLUMNS expression when creating a new table (which I was previous wrapping in the SUMMARIZE expression).
summary table =
SUMMARIZE(
'table 1',
'table 1'[category],
"summary percentage",
AVERAGE('table 1'[daily percentage])
)
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |