Need Help with Power Query

Question

I created a new column to get the monthly average unique users per dashboard:VAR DashboardDistinctUsers =&nbsp;&nbsp;&nbsp; ADDCOLUMNS(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER('raw', NOT(ISBLANK('raw'[Dashboard Name]))),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'raw'[Dashboard Name],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'raw'[Date].[Year],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'raw'[Date].[Month],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "DistinctUsers", DISTINCTCOUNT('raw'[EMAIL])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;"YearMonth", DATE([Year], [Month], 1)&nbsp;&nbsp;&nbsp; )VAR AverageDistinctUsersPerDashboard =&nbsp;&nbsp;&nbsp; SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DashboardDistinctUsers,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Dashboard Name],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "AvgDistinctUsers", AVERAGEX(DashboardDistinctUsers, [DistinctUsers])&nbsp;&nbsp;&nbsp; )VAR OverallAvgDistinctUsers =&nbsp;&nbsp;&nbsp; AVERAGEX(AverageDistinctUsersPerDashboard, [AvgDistinctUsers])RETURN&nbsp;&nbsp;&nbsp; OverallAvgDistinctUserThis gives me the following result:&nbsp;Now I want to create a new column where if the monthly avg unique users is less than 5.46 (which is the overall average) then the status should be “review relevancy”.The problem is that I can’t seem to reference that 5.46, not even with a new calculated measure.e.g. I tried the following:MEASURE Overall Monthly Average Unique Users =VAR DashboardDistinctUsers =&nbsp;&nbsp;&nbsp; ADDCOLUMNS(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER('raw', NOT(ISBLANK('raw'[Dashboard Name]))),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'raw'[Dashboard Name],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'raw'[Date].[Year],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'raw'[Date].[Month],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "DistinctUsers", DISTINCTCOUNT('raw'[EMAIL])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "YearMonth", DATE([Year], [Month], 1)&nbsp;&nbsp;&nbsp; )VAR AverageDistinctUsersPerDashboard =&nbsp;&nbsp;&nbsp; SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DashboardDistinctUsers,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Dashboard Name],&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;"AvgDistinctUsers", AVERAGEX(DashboardDistinctUsers, [DistinctUsers])&nbsp;&nbsp;&nbsp; )VAR OverallAvgDistinctUsers =&nbsp;&nbsp;&nbsp; AVERAGEX(AverageDistinctUsersPerDashboard, [AvgDistinctUsers])RETURN&nbsp;&nbsp;&nbsp; OverallAvgDistinctUsersHow can I reference the total of the table?

lbendlin · Answer

You say you need help with Power Query?&nbsp; Or did you mean DAX?&nbsp;
&nbsp;
Note that you cannot create calculated columns from measures.
&nbsp;
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 Please show the expected outcome based on the sample data you provided. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Forum Discussion

Need Help with Power Query

1 Reply

Related content

Power Query dates

Data transform using power query

ayuda en power query

Power Query

Power BI downloaded PBIX does not contain Power Query queries