cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dusha Frequent Visitor
Frequent Visitor

Filters/slicers over calculated (average) values in line chart

Hi all,

Great community, I was able to find almost all solutions and answers to the questions I've had.

But the one remains whcih I am struggling to deal with.

 

I have a simple table like below

data.PNG

 

Multiple regions, countries and location types.

3 reports for, let's say 3 years 2016, 2017, 2018 (Report column), 

12 reporting periods/months (P01-P12)

for each year (report) and period (R.Period) we may have several lines with values (Value) for same location (Region, Country and type)

 

Last column (RunningTotal) is a calcualted value, which uses a pivot calculation (total value for speicifioc year, period and location) all calculation are processed in Excel which is lated sourced by PowerBI.

In Excel I have been using a pivot chart with slicers to show 3 lines for 3 years by period (month)

Slicers were controlling Region, Country and Type. 

For the value I have been using an 'AVERAGE' from RunningTotal

 

Worked brilliant.

 

Now I am trying to achieve the same in PowerBI.

So, I have created a line chart visual with R.Period in Axis, Report (year) in legend and  RunningTotal as a value.

By default it showed the SUM, so I have changed the RunningTotal setting to 'AVERAGE'

 

Brilliant, It worked I thought....

chart.PNG



BUT when I have added 3 slicers for Region, Type and Country I have realized that they are NOT working (as they did in Excel)

After digging I have found this:

https://community.powerbi.com/t5/Community-Knowledge-Base/Calculated-Column-Table-Change-Dynamically...

 

I have tried to find a suitable workaround but failed.

I do not need a matrix table, I need a line chart as shown above with Running total, which could be controlled/filtered  by slicers/filters....

 

Any advice?
THANK YOU

UPDATE: In fact I have realized that my source table looks different from the example shown above.
'Running total' is calculated in Excel for specific 'Report' (Year) and R.Period ONLY

But while there is a Pivottable behind the RunningTotal (as shown above), the source table was recalculating the RunningTotal values when slicer filters were applied (e.g. selecting specific region or country only - recalculates the values in pivottable > values in the table column 'RunningTotal' were recalculated as well and LineChart was updated as well).

 

So in reality, file sourced to PBI looks like below (no filters applied)
data2.PNG

I guess I will need to update the RunningTotal calculation logic in the source file....
Or make the proper RunningTotal calculation within PowerBI - 

Any advice how to achieve it and get to the Linechart as shown above?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filters/slicers over calculated (average) values in line chart

@Dusha,

 

You may research the code generated by Quick measure Running total.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Dusha Frequent Visitor
Frequent Visitor

Re: Filters/slicers over calculated (average) values in line chart

I guess I might need to create a 'calculated column' in PowerBI based on 'Value', Report and R.Period...


have tried with this but numbers are not correct

 

 

Running Total COLUMN = 
CALCULATE (
SUM ( 'Table'[Value] ),
ALL ( 'Table' ),
'Table'[R.Period] <= EARLIER ( 'Table'[R.Period] )
)

R.Period is not a number/date in my case, wondering if this is causing a problem

Community Support Team
Community Support Team

Re: Filters/slicers over calculated (average) values in line chart

@Dusha,

 

You may research the code generated by Quick measure Running total.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Dusha Frequent Visitor
Frequent Visitor

Re: Filters/slicers over calculated (average) values in line chart

Great tip, thanks, I have just created 'Running total' using 'quick measure' against 'R.Period' and it worked!

All filters are working now.

 

Value running total in R.Period = 
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALLSELECTED('Table'[R.Period]),
ISONORAFTER('Table'[R.Period], MAX('Table'[R.Period]), DESC)
)
)