Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a fact table with the following columns: "Completion Date", "Email", "Specific Skill/Industry", and "Experience Level". A Microsoft form feeds an Excel table in SharePoint via Power Automate. Basically we have people do a survey of a self-evaluation on various skills. I am working on a measure wherein I was trying to build a summary table of the average scores for each Specific Skill/Industry across all respondents, then wanted to grab the minimum score and return the corresponding Specific Skill/Industry. So I built a summary table as a variable in the measure, but then I can't seem to get other variables within the measure to reference this summary table. Here is what I would like to do, but I get errors with it:
Min_Skill =
VAR Avg_Table =
SUMMARIZE (
Survey_Table,
Survey_Table[Specific Skill/Industry],
"Avg_Score", AVERAGEX ( Survey_Table, [Latest_Score] )
)
VAR Min_Val =
CALCULATE ( MIN ( Avg_Score ), ALL ( Avg_Table ) )
RETURN
CALCULATE (
MIN ( Avg_Table[Specific Skill/Industry] ),
Avg_Table[Avg_Score] = Min_Val
)
(Latest_Score is a measure I created to capture only the most recent survey responses for each person, allowing for a person to respond multiple times to the survey. For the purpose of this question, you can treat this measure and the original "Experience Level" column the same.)
The measure doesn't seem to recognize the internal links to the Avg_Table or to the Avg_Score summary column within Avg_Table. Is there any way to accomplish this without building a whole separate calculated table?
Solved! Go to Solution.
Min_Skill =
VAR Avg_Table =
// SUMMARIZE should NEVER be used to create
// aggregations. It can only ever be used
// to create groupings of rows from an extended
// table. This is the ONLY safe use of the function
// which is flawed and will not be fixed,
// as confirmed by Microsoft. But you don't need it
// here at all.
ADDCOLUMNS(
DISTINCT( Survey_Table[Specific Skill/Industry] ),
"@Avg_Score",
CALCULATE(
AVERAGEX(
Survey_Table,
[Latest_Score]
)
)
)
VAR Min_Val_Skill =
MINX(
topn(
1,
Avg_Table,
[@Avg_Score], ASC
),
Survey_Table[Specific Skill/Industry]
)
return
Min_Val_Skill
Min_Skill =
VAR Avg_Table =
// SUMMARIZE should NEVER be used to create
// aggregations. It can only ever be used
// to create groupings of rows from an extended
// table. This is the ONLY safe use of the function
// which is flawed and will not be fixed,
// as confirmed by Microsoft. But you don't need it
// here at all.
ADDCOLUMNS(
DISTINCT( Survey_Table[Specific Skill/Industry] ),
"@Avg_Score",
CALCULATE(
AVERAGEX(
Survey_Table,
[Latest_Score]
)
)
)
VAR Min_Val_Skill =
MINX(
topn(
1,
Avg_Table,
[@Avg_Score], ASC
),
Survey_Table[Specific Skill/Industry]
)
return
Min_Val_Skill
Thanks a bunch, worked like a charm.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |