Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter _ slicer _ line chart

  Hi Guys,

having this table:

TitlePeriod Mileage per period
Incident name  11000
Incident name2  11000
Incident name3  21503
Incident name4  31111
Incident name5  44503
Incident name6  44503
Incident name7  44503

 

I need to get column " incident per period value "

 

 " incident per period value " = 1000 / 2

as there are 2 incidents (or 2 rows for  period 1 ) so value is  500 

 

TitlePeriod Mileage per period incident per period value
Incident name  11000500
Incident name2  11000500
Incident name3  215031503
Incident name4  311111111
Incident name5  445031501
Incident name6  445031501
Incident name7  445031501

 

AND

put that into interactive visualisation but with filter on Title.

I want filter on Title that will change values in the graph.

 

As picture shows below:

 

after deselection of  "Incident name6" for Period 4 it shows value 3302 

but I would like to see 2251.5 

 

kind of filter doing: 

4503 / 2 = 2251.5

divide 4503 by number of Titles in period 4 BUT without "Incident name6" as it has been deselected on slicer 

Incident name5  4 4503 1501
Incident name6  4 4503 1501
Incident name7  4 4503 1501

 

 

Is it possible in Power bi?

  • PaulOlding's avatar
    PaulOlding
    4 years ago

    Hi Anonymous 

    Calculated columns aren't dynamic in Power BI.  That is, slicer selections do not affect the values in them as they are not recalculated.

    Measures are dynamic, so that's what to use.

    Here's a measure to get the values.  You may want to think about what value should be displayed at the total level.  That isn't specified in your question

    incident per period value = 
VAR _MileagePerPeriod = 
ADDCOLUMNS(
    SUMMARIZE(
        Incidents,
        Incidents[Period],
        Incidents[ Mileage per period]
    ),
    "@Rows", CALCULATE(COUNTROWS(Incidents))
)
VAR _Result = SUMX(_MileagePerPeriod, DIVIDE(Incidents[ Mileage per period], [@Rows]) )
RETURN
    _Result

     

2 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  this wil do the trick; pbix is atatched

     

    Measure =
DIVIDE (
    CALCULATE ( MAX ( tbl[ Mileage per period] ), ALLEXCEPT ( tbl, tbl[Period] ) ),
    CALCULATE ( COUNT ( tbl[Period] ), ALLEXCEPT ( tbl, tbl[Period] ) )
)

     

     

     

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous 

    Calculated columns aren't dynamic in Power BI.  That is, slicer selections do not affect the values in them as they are not recalculated.

    Measures are dynamic, so that's what to use.

    Here's a measure to get the values.  You may want to think about what value should be displayed at the total level.  That isn't specified in your question

    incident per period value = 
VAR _MileagePerPeriod = 
ADDCOLUMNS(
    SUMMARIZE(
        Incidents,
        Incidents[Period],
        Incidents[ Mileage per period]
    ),
    "@Rows", CALCULATE(COUNTROWS(Incidents))
)
VAR _Result = SUMX(_MileagePerPeriod, DIVIDE(Incidents[ Mileage per period], [@Rows]) )
RETURN
    _Result