KPI on region-lvl, vs. branch lvl

[Hugo999]

Hi everyone,

I have the following table:

branch | Region | month | kpi1 | kpi2 | kpi3 | kpi4 | monkst_KPI
--------|---------|---------|------|------|------|-------|-----------
001      | regi1   | 2212     | 0 | 0 | 0 | 0.00% | 2212_802
002      | regi1   | 2212     | 0 | 0 | 0 | 0.00% | 2212_1313
003      | regi2   | 2404     | 0 | 0 | 0 | 0.00% | 2404_493
004      | regi2   | 2404     | 0 | 0 | 0 | 0.00% | 2404_6852

123      | regi3   | 2312     | 0 | 0 | 0 | 0.00% | 2312_2280
555      | regi3   | 2410     | 0 | 0 | 0 | 0.00% | 2410_6791

etc

Field Explanation:

• KPI1-KPI4 = calculated KPI values (currently all 0)
• monKST_KPI = a concatenated key (month + KST), used as a foreign key in the data model

Currently, I have a working line chart at the KST level (one KPI vs. months). The X-axis shows months, the Y-axis shows the KPI (e.g., kpi1), and a slicer allows me to select which KST I want to display.

My question: How can I create a line chart at the Region level for adding this region-level KPI line into the same line chart? Essentially, I want to compare how the KPI for a single KST develops over time vs. the KPI for the entire region that the KST belongs to.

Any insights or guidance on setting this up would be much appreciated!

Thanks in advance!

[Anonymous]

Hi @Hugo999 ,

Have you solved your problem? If it is solved, please share your solution and accept it as solution or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu

[Anonymous · Accepted solution]

Thanks for the reply from Sahir_Maharaj.

Hi @Hugo999 ,

I created two more tables to use as slicers:

Then create two measures:

Single KST = 
VAR _region = SELECTEDVALUE('Region_slicer'[Region])
VAR _kpi = SELECTEDVALUE('KPI_slicer'[kpi])
RETURN
CALCULATE(
    SWITCH(
        _kpi,
        "kpi1", SUM('Table'[kpi1]),
        "kpi2", SUM('Table'[kpi2]),
        "kpi3", SUM('Table'[kpi3]),
        "kpi4", SUM('Table'[kpi4])
    ),
    'Table'[Region] = _region
)

All KST = 
VAR _kpi = SELECTEDVALUE('KPI_slicer'[kpi])
RETURN
CALCULATE(
    SWITCH(
        _kpi,
        "kpi1", SUM('Table'[kpi1]),
        "kpi2", SUM('Table'[kpi2]),
        "kpi3", SUM('Table'[kpi3]),
        "kpi4", SUM('Table'[kpi4])
    ),
    REMOVEFILTERS('Table'[Region])
)

Create two slicers to dynamically switch kpi and region. Result:

Best Regards,
Zhu

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

[Sahir_Maharaj]

Hello @Hugo999,

Can you please try this approach:

Region vs Branch KPI = 
VAR CurrentBranchKPI = 
    CALCULATE(
        AVERAGE('Table'[KPI1]),
        FILTER(
            'Table',
            'Table'[branch] = SELECTEDVALUE('Table'[branch])
        )
    )
VAR CurrentRegionKPI = 
    CALCULATE(
        AVERAGE('Table'[KPI1]),
        FILTER(
            'Table',
            'Table'[Region] = SELECTEDVALUE('Table'[Region])
        )
    )
RETURN
    SWITCH(
        TRUE(),
        ISINSCOPE('Table'[branch]), CurrentBranchKPI,
        ISINSCOPE('Table'[Region]), CurrentRegionKPI,
        BLANK()
    )