Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear community,
I have the following table
I want to sum the values on the purple column depending on the value of the green column,
Example: All the "6" on the green green column shoul have the same value on the last column, the "5" the same value etc.
How can I do this?
Regards.
Solved! Go to Solution.
Hi Kevs
Summing up total parts for each month
Group by the Month Column (Green Column):
Click on the green column (Month).
Go to the "Transform" tab, then click "Group By."
In the "Group By" window:
Group By: "Month" (or the name of the green column)
New column name: Enter a name for the new column (e.g., "Total Parts per Month")
Operation: Select "Sum"
Column: Choose the purple column (Total Parts)
Click "OK."
Merge the Summed Values Back to the Original Table:
Go to the "Home" tab, then click "Merge Queries."
Select the original table and the newly created grouped table.
Match the Month columns in both tables.
Choose "Inner" join, then click "OK."
Expand the newly created column to include the summed value.
Rename and Reorder Columns (Optional):
You can rename the new column and reorder the columns as needed.
Apply and Close:
Click "Close & Apply" in Power BI or "Close & Load" in Excel.
You should now have a table where each row contains the total parts for the corresponding month in the green column. All rows with the same month number will have the same value in the new column. This process groups the data by month and calculates the sum of total parts for each month, then merges that information back into the original table.
Hi Kevs,
Create a Measure for Total Parts by Month:
You can create a measure that calculates the total parts for each month, considering the conditions in your MonthKPI2 table. Sample code snippet:
TotalPartsByMonth =
SUMX(
FILTER(
ALL(DATABASE),
DATABASE[KP] = "2 - Despues Spray" &&
DATABASE[Fecha].[MonthNo] = SELECTEDVALUE(DATABASE[Fecha].[MonthNo])
),
DATABASE[Total Parts]
)
Use this measure in your summarized table to get the total parts for each month. This measure considers the filter context and calculates the sum based on the selected month.
Add this measure to any visual in Power BI, and it should calculate the total parts for each month based on the existing filters and slicers. By creating a measure, you avoid modifying the main database or the incremental refresh policy, and you can still achieve the desired summation based on the green column (month numbers).Please remember to replace DATABASE[Total Parts] with the actual column name representing the purple column in your table.
Hi Kevs,
Create a Measure for Total Parts by Month:
You can create a measure that calculates the total parts for each month, considering the conditions in your MonthKPI2 table. Sample code snippet:
TotalPartsByMonth =
SUMX(
FILTER(
ALL(DATABASE),
DATABASE[KP] = "2 - Despues Spray" &&
DATABASE[Fecha].[MonthNo] = SELECTEDVALUE(DATABASE[Fecha].[MonthNo])
),
DATABASE[Total Parts]
)
Use this measure in your summarized table to get the total parts for each month. This measure considers the filter context and calculates the sum based on the selected month.
Add this measure to any visual in Power BI, and it should calculate the total parts for each month based on the existing filters and slicers. By creating a measure, you avoid modifying the main database or the incremental refresh policy, and you can still achieve the desired summation based on the green column (month numbers).Please remember to replace DATABASE[Total Parts] with the actual column name representing the purple column in your table.
Hi Kevs
Summing up total parts for each month
Group by the Month Column (Green Column):
Click on the green column (Month).
Go to the "Transform" tab, then click "Group By."
In the "Group By" window:
Group By: "Month" (or the name of the green column)
New column name: Enter a name for the new column (e.g., "Total Parts per Month")
Operation: Select "Sum"
Column: Choose the purple column (Total Parts)
Click "OK."
Merge the Summed Values Back to the Original Table:
Go to the "Home" tab, then click "Merge Queries."
Select the original table and the newly created grouped table.
Match the Month columns in both tables.
Choose "Inner" join, then click "OK."
Expand the newly created column to include the summed value.
Rename and Reorder Columns (Optional):
You can rename the new column and reorder the columns as needed.
Apply and Close:
Click "Close & Apply" in Power BI or "Close & Load" in Excel.
You should now have a table where each row contains the total parts for the corresponding month in the green column. All rows with the same month number will have the same value in the new column. This process groups the data by month and calculates the sum of total parts for each month, then merges that information back into the original table.
Hi NareM
I try but I get this warning:
I would not like to delete my refresh.
I tried that on the main database (the firts pic is a summerized table), I would like to have that on the summarized table
Is there any other way to do it with code?
Thanks.