Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey everyone,
First time working with PowerBI and having a bit of trouble with understanding the slicers and running total.
I am trying to track equipment deployment over months and wanted to compare them year to year.
I needed the running total to not reset each year as we need to know that 10 units were deployed in December 2010 and then +1 (11) in January 2011.
I'm also trying to show this in a line chart with the series being each year.
However, I also have another splicer for Company which will need to recalculate the running total if a specific company is selected.
(To add to the complication, I have another slicer for products but we can ignore that for now)
Ex)
I'm using the year slicer as a way to filter out the other series / lines.
How do I go about setting the running total to only be affected by the company slicer and not the year slicer?
Formulas I have tried:
Attempt 1:
Solved! Go to Solution.
Figured it out!
To calculate the running total while preserving filters from other tables and excluding the date table, I had to modify the FILTER function.
NetQuantityRunningTotal =
CALCULATE(
SUM('transactionrecord'[net_quantity]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
By calling ALL on the Date Table, I was able to remove any filtering on the date table.
Running total was preserved across the entire time span and now when I filter by the date, the initial value does not reset to '0'.
Thanks for the assistance!
Hi I am using a running 12 month total measure like below
Well, seems like ALLEXCEPT should be your path to victory, what problem were you having? You would want to use something like ALLEXCEPT('Table'[Company])
Thanks for the quick response!
So my 'contractor name' is part of another table, so when I use the ALLEXCEPT and provide the contractor table, the conditional line that comes after cannot find 'transaction_date'.
NetQuantityRunningTotal =
CALCULATE(
SUM('transactionrecord'[net_quantity]),
FILTER(
ALLEXCEPT('contractor', 'contractor'[Contractor Name]),
'transactionrecord'[transaction_date] <= MAX('Date'[Date])
)
)
Figured it out!
To calculate the running total while preserving filters from other tables and excluding the date table, I had to modify the FILTER function.
NetQuantityRunningTotal =
CALCULATE(
SUM('transactionrecord'[net_quantity]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
By calling ALL on the Date Table, I was able to remove any filtering on the date table.
Running total was preserved across the entire time span and now when I filter by the date, the initial value does not reset to '0'.
Thanks for the assistance!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |