Forum Discussion
Sum per Trade per Area
Hi good day can anyone help me on my calculated column, what i require is sum of weekly plan per Trade per Area.
DESIRED OUTPUT
Thank you
AllanBerces , In DAX
New column =
sumx(filter(Table, [Area] = earlier([Area]) && [Trade] =earlier([Trade]) ), [Weekly_plan])
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/cyWVzAQF9YU?t=32060
Hi AllanBerces
Can you please try the below dax ?Total_Trade =CALCULATE(SUM('Table'[Weekly Plan]),ALLEXCEPT('Table', 'Table'[Area], 'Table'[Trade]))
If this answers your questions, kindly accept it as a solution and give kudos.Hello AllanBerces,
Thank you for reaching out to the Microsoft Fabric Community Forum.
I have reproduced your scenario in Power BI Desktop using the exact structure you provided, and I was able to achieve the expected output by creating a calculated column that returns the sum of Weekly_Plan per Trade and Area.Steps I Followed:
- Created a table with your data structure: Columns>> Area, Week No., Weekly_Plan, Trade
- Added the following DAX Calculated Column:
Total_Trade =
CALCULATE(
SUM('Table'[Weekly_Plan]),
ALLEXCEPT('Table', 'Table'[Area], 'Table'[Trade])
)
- The output now shows the total Weekly_Plan grouped by both Area and Trade, exactly as you requested.
Output:
Please find the attached .pbix file that demonstrates the solution in action and also thanks to mdaatifraza5556 & amitchandak for sharing valuable insights.
Best Regards,
Ganesh singamshetty.
5 Replies
AllanBerces , In DAX
New column =
sumx(filter(Table, [Area] = earlier([Area]) && [Trade] =earlier([Trade]) ), [Weekly_plan])
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/cyWVzAQF9YU?t=32060
Hi amitchandak mdaatifraza5556 v-ssriganesh thank you very much for the reply working as i need.
Hi AllanBerces
Can you please try the below dax ?Total_Trade =CALCULATE(SUM('Table'[Weekly Plan]),ALLEXCEPT('Table', 'Table'[Area], 'Table'[Trade]))
If this answers your questions, kindly accept it as a solution and give kudos.Hello AllanBerces,
Thank you for reaching out to the Microsoft Fabric Community Forum.
I have reproduced your scenario in Power BI Desktop using the exact structure you provided, and I was able to achieve the expected output by creating a calculated column that returns the sum of Weekly_Plan per Trade and Area.Steps I Followed:
- Created a table with your data structure: Columns>> Area, Week No., Weekly_Plan, Trade
- Added the following DAX Calculated Column:
Total_Trade =
CALCULATE(
SUM('Table'[Weekly_Plan]),
ALLEXCEPT('Table', 'Table'[Area], 'Table'[Trade])
)
- The output now shows the total Weekly_Plan grouped by both Area and Trade, exactly as you requested.
Output:
Please find the attached .pbix file that demonstrates the solution in action and also thanks to mdaatifraza5556 & amitchandak for sharing valuable insights.
Best Regards,
Ganesh singamshetty.Hi,
This calculated column formula works
=calculate(sum(Data[weekly_plan]),filter(Data,Data[Area]=earlier(Data[Area])&&Data[Trade]=earlier(Data[Trade])))
Hope this helps.
