Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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....
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....
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)
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.
You may research the code generated by Quick measure Running total.
You may research the code generated by Quick measure Running total.
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)
)
)
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |