Forum Discussion

Lucra's avatar
Lucra
Frequent Visitor
2 years ago

Dynamic Measures with Calculation Group for Actual vs. Target KPI

Have an interesting scenario with calculation groups, field parameters, and dynamic measures, and KPI cards.

 

Field parameter:

Measure Parameter 1 = {
    ("Sales", NAMEOF('Measure Table'[Sales]), 0),
    ("Cost", NAMEOF('Measure Table'[Cost]), 1),
    ("Gross Profit", NAMEOF('Measure Table'[Gross Profit]), 2))
}
 
Calculation group:

 

Dynamic measure:

Dynamic Measure Base =
VAR SelectedMeasureIndex = SELECTEDVALUE('Measure Parameter 1'[Parameter Order], -1)
RETURN
    SWITCH(
        SelectedMeasureIndex,
        0, [Sales],
        1, [Cost],
        2, [Gross Profit]
)
 
Everything works correctly using basic visuals and a Period slicer on the canvas to drive the calc group and measure selection.


However, for a KPI card or any visual in which you want to show Actual compared to Target, you need separate measures. Unfortunately, when applying the Period filter to the visual you end up with unexpected results as both measures will be impacted by the same Period in the calc group. You can overcome this by creating a specific measure such as this:
 
Dynamic Measure Target =
CALCULATE(
    [Dynamic Measure Base],
    FILTER(
        ALL('Time Intelligence')
        ,'Time Intelligence'[Period] = "PWTD"
    )
)
 
But now you're defeating the purpose of calculation groups by still building a bunch of specific measures with filtering logic.
 
Theoretically, I would expect to be able to dynamically adjust the filter for the calc group by doing something like this:

Dynamic Measure Target =
VAR currentPeriod = SELECTEDVALUE( 'Time Intelligence'[Period] )
VAR nextPeriod = "P" & currentPeriod
--VAR nextPeriod = SELECTEDVALUE('Time Intelligence Selector'[Next Period])
VAR result =
CALCULATE(
    [Dynamic Measure Base],
    FILTER(
        ALL('Time Intelligence')
        ,'Time Intelligence'[Period] = nextPeriod
    )
)

RETURN
result

I even tried to create a calculated table with the nextPeriod and adjust the measure like this:
Dynamic Measure Target =
--VAR currentPeriod = SELECTEDVALUE( 'Time Intelligence'[Period] )
--VAR nextPeriod = "P" & currentPeriod
VAR nextPeriod = SELECTEDVALUE('Time Intelligence Selector'[Next Period])
VAR result =
CALCULATE(
    [Dynamic Measure Base],
    FILTER(
        ALL('Time Intelligence')
        ,'Time Intelligence'[Period] = nextPeriod
    )
)

RETURN
result
 
I confirmed the periods are being returned correct in both scenarios:
 

 

Yet, the visuals still fail / timeout due to memory issues, but if hardcoded to = "PWTD" they work just fine.

 

Would love to know if I'm simply missing something or if this isn't possible.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lucra ,

    Based on the description, may be the periods had been returned correct.

    View the following document to learn more information.

    Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn

     

    If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

    You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    Best Regards,

    Wisdom Wu

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

    • Lucra's avatar
      Lucra
      Frequent Visitor
      2 years ago

      Good morning and thank you for your reply. I reviewed the documentation you linked and believe I have everything working. The documentation refers to using the group in a measure like this:

       

      In this format I can use the groups without issue. When things stop working is passing in a dynamic value instead of "YOY%" like this:

       

      Dynamic Measure Target =
VAR nextPeriod = SELECTEDVALUE('Time Intelligence Selector'[Next Period])
VAR result =
CALCULATE(
    [Dynamic Measure Base],
    FILTER(
        ALL('Time Intelligence')
        ,'Time Intelligence'[Period] = nextPeriod
    )

       

      From what I've read, dynamically assigning a group item doesn't appear to be supported. I'd love for someone to tell me otherwise, though.