Forum Discussion
Running Total on a non-date column
Hi guys,
I am having some real problems with calculating a running-total in Power BI.
Main problem here is that my running total should be based on a calculated measure.
Here is the problem:
Calculate a column, that will show, if your product is in the product group, that makes 80% of sales per Brand.
Someting like this (the problem is - how to calculate column E):
I have tried several tutorials and forums but still no answer.
Looks like the support bot running functions is very limited in Power BI.
Thanks
Please try with following Calculated Measure for the cumulative sales. It should be able to reduce the calculation.
Cumulative_Sales_Measure = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL(Table1), Table1[Sales] >= MAX ( Table1[Sales] ) ), VALUES ( Table1[Brand] ) )
Regards,
10 Replies
In this scenario, you can rank the sales for each brand first, and then get the cumulative sales according to the rank. Please create two calculated columns with following formulas.
RankInBrand = RANKX ( FILTER ( Table1, EARLIER ( Table1[Brand] ) = Table1[Brand] ), Table1[Sales] )Cumulative Sales = SUMX ( FILTER ( Table1, Table1[RankInBrand] <= EARLIER ( Table1[RankInBrand] ) && Table1[Brand] = EARLIER ( Table1[Brand] ) ), Table1[Sales] )Regards,
Thanks for the reply..
I got stuck on
RANKX ( FILTER ( Table1, EARLIER ( Table1[Brand] ) = Table1[Brand] ), Table1[Sales] )EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
Looking for a workaround...
Please make sure you’re creating a Calculated Column instead of a Measure with above formulas.
Regards,
Thanks a lot for the solution, I was struggling to get the cumulative total
Hi Guys this does not seem to work .
Product NameProduct Sales
Product 63 25145 Product 64 25413 Product 65 25681 Product 66 25949 Product 67 26217 Product 68 26485 Product 69 26753 Product 70 27021 Product 71 27289 Product 72 27557 Product 73 27825 Product 74 28093 Product 75 28361 Product 76 28629 Product 77 28897 Product 78 29165 Product 79 29433 Cumulative_Sales_Measure =
CALCULATE (
SUM ( Product[Total Sales] ),
FILTER ( ALL(Product), Product[Total Sales] >= MAX ( Product[Total Sales] ) ),
VALUES ( Product[Product Name )
)
I have same query no body going to resolve this problem even there is no toturial for this concern.
Name Retailer Channel Val Contri Flag Town Level Achampet R2 C1 42 36% Top 80% Achampet R1 C2 30 26% Top 80% Achampet R3 C1 26 22% Top 80% Achampet R4 C2 19 16% Rest ADDANKI R7 C1 45 38% Top 80% ADDANKI R8 C2 26 22% Top 80% ADDANKI R5 C1 21 18% Top 80% ADDANKI R9 C2 13 11% Rest ADDANKI R6 C1 12 10% Rest ADILABAD R11 C2 75 35% Top 80% ADILABAD R10 C1 44 21% Top 80% ADILABAD R13 C2 33 15% Top 80% ADILABAD R14 C1 31 15% Top 80% ADILABAD R12 C2 30 14% Rest
