Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, dear comunity:
I'm here cause need help with doing a cumulative sum of sales wich I calculated it with SUM(), need to be representated on a pivot table in excel or matrix in Power BI with DAX. If I do a sum of sales and bring customername to labels I have this result, and I need a solution to receive the orange column's behavior. the sales must be ordered by "Total sales in period" value.
I got the sample data in this url to practice: Sample Sales Data (kaggle.com)
Sorry for my bad grammar in english, I'm still learning.
Kind regards.
Solved! Go to Solution.
Measure =
CALCULATE (
[Sales],
WINDOW (
1, ABS, 0, REL,
ALL ( 'Table'[Etiquetas de fila] ),
ORDERBY ( [Sales], DESC )
)
)
or
Measure 2 =
VAR CustomerSales =
ADDCOLUMNS (
ALLSELECTED ( 'Table'[Etiquetas de fila] ),
"@Sales", [Sales]
)
VAR Result =
SUMX (
WINDOW (
1, ABS, 0, REL,
CustomerSales,
ORDERBY ( [@Sales], DESC )
),
[@Sales]
)
RETURN
Result
Measure =
CALCULATE (
[Sales],
WINDOW (
1, ABS, 0, REL,
ALL ( 'Table'[Etiquetas de fila] ),
ORDERBY ( [Sales], DESC )
)
)
or
Measure 2 =
VAR CustomerSales =
ADDCOLUMNS (
ALLSELECTED ( 'Table'[Etiquetas de fila] ),
"@Sales", [Sales]
)
VAR Result =
SUMX (
WINDOW (
1, ABS, 0, REL,
CustomerSales,
ORDERBY ( [@Sales], DESC )
),
[@Sales]
)
RETURN
Result
It actually worked!
I didn't knew about WINDOW function I investigated documentation and learned a lot from you.
Thank u very much!
Hi @TRIXTERBINOOB ,
You can achive your required output in many different ways and the following is an example.
First create a ranking of sales by customers.
Then, create a cumulative total in the order of customer sales ranking by value.
I attach an example pbix file below:
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |