Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I have a cumulative column working using quick measure:
Cumulative Amount= CALCULATE (
ORDER_DATE | Amount | Cumulative Amount |
JAN | 10 | 10 |
FEB | 5 | 15 |
MAR | 5 | 20 |
However when I add another column (client type) the monthly totals are duplicating inthe cumulative column.
How do I get cumulative totals per the client type by month?
ORDER_DATE | CLIENT TYPE | Amount | Cumulative Amount |
JAN | A | 2 | 10 |
JAN | B | 5 | 10 |
JAN | C | 3 | 10 |
FEB | A | 1 | 15 |
FEB | B | 3 | 15 |
FEB | C | 1 | 15 |
MAR | A | 3 | 20 |
MAR | B | 1 | 20 |
MAR | C | 1 | 20 |
So I would like this:
ORDER_DATE | CLIENT TYPE | Amount | Cumulative Amount |
JAN | A | 2 | 2 |
JAN | B | 5 | 5 |
JAN | C | 3 | 3 |
FEB | A | 1 | 3 |
FEB | B | 3 | 8 |
FEB | C | 1 | 4 |
MAR | A | 3 | 6 |
MAR | B | 1 | 9 |
MAR | C | 1 | 5 |
Solved! Go to Solution.
@jp2020 , Try if this can help
Cumulative Amount= CALCULATE (
SUM('Table'[Amount] ),
FILTER (ALLSELECTED('Table'),'NT Transactions'[Client_Type] =max('NT Transactions'[Client_Type] ) &&
'NT Transactions'[ORDER_DATE] <= MAX( 'NT Transactions'[ORDER_DATE] )
)
)
@jp2020 , Try if this can help
Cumulative Amount= CALCULATE (
SUM('Table'[Amount] ),
FILTER (ALLSELECTED('Table'),'NT Transactions'[Client_Type] =max('NT Transactions'[Client_Type] ) &&
'NT Transactions'[ORDER_DATE] <= MAX( 'NT Transactions'[ORDER_DATE] )
)
)
Hi!
I tried this and it works! Thanks! But I have met another problem. If client C does not have any order in MAR I would like that it shows me the number from the previous month (like 4+0=4), but client C does not appear at all. Is it some way to do it like this? All clients are showen every month with result to this month.
That worked perfectly, thank you!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |