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.
dax expert help me pls
calculated cumulative planned column and calculated cumulative actual column, how can we get
category wise?
Solved! Go to Solution.
@ukantkumar do you want cummulative total by date for each category, your output doesn't represents that thou.
If you want it the order it is in, add index field using power query and then use following measure
**bleep** Actual =
CALCULATE( SUM( Table1[Actual] ),
FILTER( ALLEXCEPT( Table1, Table1[Category] ), Table1[index] <= MAX( Table1[Index] ) )) **bleep** Planned =
CALCULATE( SUM( Table1[Planned] ),
FILTER( ALLEXCEPT( Table1, Table1[Category] ), Table1[index] <= MAX( Table1[Index] ) ))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table. To your Table visual, drag Date from the Calendar Table and Category from the Data Table. Write these measures
Planned Total = SUM(Data[Planned])
Actual Total = SUM(Data[Actual])
Cumulative planned = CALCULATE([Planned Total],DATESYTD(Calendar[Date],"31/12/")
Cumulative Actual = CALCULATE([Actual Total],DATESYTD(Calendar[Date],"31/12/")
Hope this helps.
@ukantkumar do you want cummulative total by date for each category, your output doesn't represents that thou.
If you want it the order it is in, add index field using power query and then use following measure
**bleep** Actual =
CALCULATE( SUM( Table1[Actual] ),
FILTER( ALLEXCEPT( Table1, Table1[Category] ), Table1[index] <= MAX( Table1[Index] ) )) **bleep** Planned =
CALCULATE( SUM( Table1[Planned] ),
FILTER( ALLEXCEPT( Table1, Table1[Category] ), Table1[index] <= MAX( Table1[Index] ) ))
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
thanks
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 |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |