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.
Hello everyone,
Below is my sample data.
ITEM CATEGORY DATE AMOUNT RUNNING TOTAL
1 c1 1/1/2020 100 100
1 c1 2/1/2020 150 250
1 c2 1/1/2020 200 200
1 c3 2/1/2020 150 150
2 c1 2/1/2020 200 200
2 c2 1/1/2020 250 250
2 c3 1/2/2020 100 100
2 c3 2/2/2020 150 250
The last calculated column named running total is what my requirement. I need to create cumulative amount column based on 3 columns(item,category,date). First i need to filter item column and then category, to calculate the sum of amount according to 'table1'[date]<=EARLIER('table1'[date]) in ascending order. Most import thing is my report page will only have date slicer, item and category slicer will not be presented. My visual is a table visual.
Thanks in advance....
Solved! Go to Solution.
Try as column
cumm column= sumx(filter(Table, [ITEM] = earlier([ITEM]) && [CATEGORY] = earlier([CATEGORY]) && [DATE] <= earlier([DATE]) ),[AMOUNT])
As measure
cumm measure= sumx(filter(allselected(Table), [ITEM] = max([ITEM]) && [CATEGORY] = max([CATEGORY]) && [DATE] <= max([DATE]) ),[AMOUNT])
@RohiniP-26
Add as a new Column:
Cumm =
var i = [ITEM ]
var cat = [CATEGORY ]
var _date = [DATE ]
return
CALCULATE(
SUM('Table'[AMOUNT ]),
'Table'[ITEM ] = i,
'Table'[CATEGORY ] = cat,
'Table'[DATE ] <= _date,
ALL('Table')
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@RohiniP-26
Add as a new Column:
Cumm =
var i = [ITEM ]
var cat = [CATEGORY ]
var _date = [DATE ]
return
CALCULATE(
SUM('Table'[AMOUNT ]),
'Table'[ITEM ] = i,
'Table'[CATEGORY ] = cat,
'Table'[DATE ] <= _date,
ALL('Table')
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Try as column
cumm column= sumx(filter(Table, [ITEM] = earlier([ITEM]) && [CATEGORY] = earlier([CATEGORY]) && [DATE] <= earlier([DATE]) ),[AMOUNT])
As measure
cumm measure= sumx(filter(allselected(Table), [ITEM] = max([ITEM]) && [CATEGORY] = max([CATEGORY]) && [DATE] <= max([DATE]) ),[AMOUNT])
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 |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |