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.
Hi All,
I have a summarize function from the below code:
VAR v1 =
SUMMARIZE(
FILTER(
FactSales,
FactSales[AsOfDate] = CALCULATE(MAX(FactSales[AsOfDate]), ALL(DimDate))
),
DimDate[Year],
"Last As Of Date", MAX(FactSales[AsOfDate]),
"Last Sales", LASTNONBLANK(FactSales[Sales], 0)
)
Which provides this output
Year | Last As Of Date | Last Sales |
2023 | 28-02-2023 00:00:00 | 1037467263,3053 |
2022 | 31-12-2022 00:00:00 | 2132113394,1337 |
2021 | 31-12-2021 00:00:00 | 2726595255,5036 |
2020 | 31-12-2020 00:00:00 | 4503000000 |
2019 | 31-12-2019 00:00:00 | 4372000000 |
2018 | 31-12-2018 00:00:00 | 3985000000 |
2017 | 31-12-2017 00:00:00 | 3681000000 |
2016 | 31-12-2016 00:00:00 | 3101000000 |
2015 | 31-12-2015 00:00:00 | 2607000000 |
2014 | 31-12-2014 00:00:00 | 2245000000 |
2013 | 31-12-2013 00:00:00 | 2144000000 |
2012 | 31-12-2012 00:00:00 | 1443300000 |
2011 | 31-12-2011 00:00:00 | 1470900000 |
2010 | 31-12-2010 00:00:00 | 3486451000 |
2009 | 31-12-2009 00:00:00 | 3520772000 |
What I then need is a cumulative sale based on the above table, but I cannot figure it out. I have tried using the SUMX function but it does not seem to work.
Can someone help me out on this?
Best Regards,
Solle
Hello Jeppe,
By cumulative sum, do you mean you would like 2009 to show 2009 sales, 2010 to show 2009-2010 sales, 2011 to show 2009-2011 sales, etc.?
If so, knowing nothing else about your model beyond this table (and assuming you would like this as a calculated column), you can try something like:
Cumulative Sales =
-- This variable filters for only the rows that are before or equal to the date in the table row
VAR FilteredTable =
FILTER (
Table1,
Table1[Last Date] <= EARLIER ( Table1[Last Date] )
)
-- This variable then takes only the filtered rows from the above table and sums up all the sales values
VAR Result =
SUMX (
FilteredTable,
[Last Sales]
)
RETURN Result
Please let me know if you were looking for something different, Jeppe! 😄
Hi @Wilson_
Thank you for taking your time to respond - I see that my code snippet from the summarize function didnt show properly, I have just updated my current summarize function, which I prefer to evolve into a cumulative DAX measure.
Yes I mean cumulative like you show above. Your code unfortunately does not apply as expected due to my summarize function I assume. Do you have any idea on how to fix the cumulative function?
Best Regards,
Solle
Hi @Wilson_
I think your solution using SUMX correctly cumulates the data, but how can I avoid it from being affect from a date slicer, which is on my page? As what I want to accomplish is to always show the total no matter what dates are selected.
VAR v1 =
SUMMARIZE(
FILTER(
FactSales,
FactSales[AsOfDate] = CALCULATE(MAX(FactSales[AsOfDate]), ALL(DimDate))
),
DimDate[Year],
"Last As Of Date", MAX(FactSales[AsOfDate]),
"Last Sales", LASTNONBLANK(FactSales[Sales], 0)
)
VAR Result =
SUMX(v1,
[Last Sales]
)
RETURN
Result
Best Regards,
Hi Solle,
Can you help clarify your request? If you want one cumulative total amount that ignores the date slicer, are you not just looking for total sales? If that's not what you're looking for, what number are you expecting this measure to return, based on the results of the original post's table?
Hi @Wilson_ ,
I am actually getting what I want but not just for the subtotals, which was the one I was focussing on, I didnt realize that the subtotal could sum to something different than the above rows.
So I am achieving what I seek but for the totals/subtotals in my table I am getting some very weird values, which does not correspond to the rows above, do you know how to fix this?
Product Category | Sub Product Category | Cumulative Sales |
1 | 1 | 927 |
1 | 2 | 7.953 |
1 | 3 | |
1 | 4 | 245 |
1 | 5 | |
1 | 6 | 1.216 |
1 | Subtotal | 7.953 |
2 | Subtotal | 792 |
3 | Subtotal | 2.761 |
Total | 7.953 |
I have attached the results from my matrix where the correct subtotal for product category 1 should be 10.341.
Best Regards,
Solle
Hey Solle,
I think at this point, it might be easier for me if you could provide your pbix file (or a sanitized/smaller version) so I can look under the hood. 😄
Try this:
Measure =
VAR _Currentyear = SELECTEDVALUE('sales'[Year])
RETURN IF(HASONEFILTER(sales[Year]),sumx(FILTER(ALLSELECTED('sales'),'sales'[Year]<=_Currentyear),'sales'[Last Sales]),sum(sales[Last Sales]))
Proud to be a Super User!
Hi @andhiii079845
Thank you for taking your time to respond. I have jut tried your code, but as my summarize code didnt show properly, I am unfortunate that your code snippet does not work, do you have any better suggestion?
Best Regards,
Solle
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 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |