How to Dynamically Evaluate any Measure Over a Defined Summary Table

[kgoth]

Hi All,

I'm reaching out for help as I feel I've exhausted my search online and other options 😅

Goal:

I want to create a calculation item in Power BI that dynamically evaluates a selected measure over a predefined summary table.

Context:

I've defined a summary table using multiple variables to return the 'busiest hour' across specific date and location attributes. The result (filtered) can look something like this:

Location Field 1	Location Field 2	Date	Hour
Location A	Area 1	22/08/24	04:00 - 05:00
Location A	Area 1	23/08/24	05:00 - 06:00
Location A	Area 1	24/08/24	13:00 - 14:00
Location A	Area 1	25/08/24	04:00 - 05:00

I need a dynamic calculation that can evaluate existing measures - i.e., Totals, Averages, Counts, Percentages - over the rows of the summary table and return a singular value as needed.

Problem(s):

• I'm unable to use typical iterative DAX functions like SUMX or AVERAGEX since the measures I want to evaluate would require different calculations.
  • Essentially, I need the expression defined in the measures to be evaluated for each row.
• I tried using SUMMARIZE to return a single value, but it doesn't carry over the filter context from the summary table - i.e., it would attempt to evaluate over every possible context in the summary table, like:

Location Field 1	Location Field 2	Date	Hour	Measure
Location A	Area 1	22/08/24	04:00 - 05:00	-
Location A	Area 1	22/08/24	05:00 - 06:00	-
Location A	Area 1	22/08/24	13:00 - 14:00	-

and so on...

Any help / suggestions / alternatives would be highly appreciated!

Thanks 😁

[Anonymous · Accepted solution]

Hi @kgoth 

Thank you very much lbendlin and DataInsights for your prompt reply. Allow me to share something.

For your question, here is the method I provided:

Here's some dummy data

“Table”

Total Measure = SUM('Table'[Value])
Avg Measure = AVERAGE('Table'[Value]) 
Count Measure = COUNTROWS('Table')
Per Measure = DIVIDE(SUM('Table'[Value]), CALCULATE(SUM('Table'[Value]), ALL('Table')))

Create a calculation table, using CALCULATETABLE to create a table that holds the filtering context. To add columns with measures, use ADDCOLUMNS to add the measures you want to evaluate to this table.

EvaluatedMeasuresTable = 
ADDCOLUMNS(
    CALCULATETABLE(
        'Table',
        ALLSELECTED('Table')
    ),
    "Total", 'Table'[Total Measure],
    "Average", 'Table'[Avg Measure],
    "Count", 'Table'[Count Measure],
    "Percentage", 'Table'[Per Measure]
)

Create a new table for selecting measures.

MeasureSelector = 
DATATABLE(
    "MeasureName", STRING,
    {
        {"Total"},
        {"Average"},
        {"Count"},
        {"Percentage"}
    }
)

Use SELECTEDVALUE to dynamically select the measures to be evaluated.

DynamicMeasure = 
VAR SelectedMeasure = SELECTEDVALUE('MeasureSelector'[MeasureName])
RETURN
    SWITCH(
        SelectedMeasure,
        "Total", 'Table'[Total Measure],
        "Average", 'Table'[Avg Measure],
        "Count", 'Table'[Count Measure],
        "Percentage", 'Table'[Per Measure],
        BLANK()
    )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@kgoth,

Here's an idea for a calculation item. If I understand the requirement correctly, you want the aggregation type to be determined by the selected measure. You can use SELECTEDMEASURENAME instead of ISSELECTEDMEASURE to search for name patterns.

Calculation Item =
VAR vTable =
    ADDCOLUMNS ( SummaryTable, "@Measure", SELECTEDMEASURE () )
VAR vResult =
    SWITCH (
        TRUE,
        ISSELECTEDMEASURE ( [Example Sum Measure] ), SUMX ( vTable, [@Measure] ),
        ISSELECTEDMEASURE ( [Example Average Measure] ), AVERAGEX ( vTable, [@Measure] )
    )
RETURN
    vResult

[lbendlin]

I need the expression defined in the measures to be evaluated 

Power BI sadly doesn't support EVALUATE for this.  

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com

[kgoth]

I'm not trying to use EVALUATE as a DAX function, I'm just using it in this context as a verb.

[lbendlin]

If you could use it as a DAX function it would solve your issue.