Forum Discussion

kgoth's avatar
kgoth
Frequent Visitor
2 years ago
Solved

How to Dynamically Evaluate any Measure Over a Defined Summary Table

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 1Location Field 2DateHour
Location AArea 122/08/2404:00 - 05:00
Location AArea 123/08/2405:00 - 06:00
Location AArea 124/08/2413:00 - 14:00
Location AArea 125/08/2404: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 1Location Field 2DateHourMeasure
Location AArea 122/08/2404:00 - 05:00-
Location AArea 122/08/2405:00 - 06:00-
Location AArea 122/08/2413:00 - 14:00-

and so on...

 

Any help / suggestions / alternatives would be highly appreciated!

 

Thanks 😁

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago
    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's avatar
      kgoth
      Frequent Visitor
      2 years ago

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

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.