Forum Discussion

Gruja's avatar
Gruja
New Member
1 year ago
Solved

Link table data with measures data and display in a visual

I’m working on a Power BI report and need assistance with combining forecasted and actual space data, while ensuring the ability to filter the data by Kostenstelle (department). Below is my data mode...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Gruja 

     

    ComparisonTable is a calculated table. A calculated table cannot be filtered by filters or slicers in the report. You can consider its data is static when you interact with the report. 

     

    To have a dynamic result displayed, you need to use a measure to populate the column visual. 

     

    A common practice is to add an additional table which has category values to be displayed on x-axis. For example, table 'DimCategory':

     

    Create measures to calculate the Forecasted value and Actual value separately. For example, 

    Forecasted = 
    SWITCH(
        SELECTEDVALUE('DimCategory'[Category]),
        "Office Space", [Bürobedarf],
        "Storage Space", [Lagerbedarf], 
        "Laboratory Space", [Gesamt Laborbedarf]
    )
    Actual = 
    SWITCH(
        SELECTEDVALUE('DimCategory'[Category]),
        "Office Space", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1] = "NUF2"), 'Export RDB'[Raum - Fläche in m²]),
        "Storage Space", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1]  = "NUF4"),'Export RDB'[Raum - Fläche in m²]), 
        "Laboratory Space", SUMX(FILTER('Export RDB', 'Export RDB'[DIN277-1]= "NUF3"), 'Export RDB'[Raum - Fläche in m²])
    )

     

    Place the new 'DimCategory' table's [Category] column to X-axis, and place the two new measures to Y-axis. 

     

    Hope this would be helpful. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!