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
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
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....
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:
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....
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)
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?
Solved! Go to Solution.
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
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 =
ISONORAFTER('Table'[R.Period], MAX('Table'[R.Period]), DESC)