Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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,
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 |
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 )
)
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 a lot for the solution, I was struggling to get the cumulative total
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,
S.no | Job Name | Fail_count | Running_fail_count_Sum |
1 | sapbw | 110 | 110 |
2 | Sslog | 90 | 200 |
3 | tdnxt | 63 | 263 |
4 | rtxls | 22 | 285 |
How do i get the S.No generated based on Fail_count ? and running_count as shown in the sample. .. Job_name and Fail_count is available.
Thanks Simon,
This solution might work in theory, but the calculation eats all of my RAM (13 GB) and drops due to lack of space.
Perhaps Power BI just can't handle those calculations. I would probably have to push that to a Calculated Measure and this may be impossible.
Such a simple task, yet so hard to implement.
Thanks for the help.
Hi!
Did you found a solution to the memory problem? I have the same problem, we are trying to do the same exercise you put on the table.
Regards
JCarlos
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,
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |