Forum Discussion
Sum
Dear friend,
i have data like:
| Category | Sub category | value |
| A | A1 | 5 |
| A | A1 | 10 |
| A | A2 | 3 |
| B | B1 | 6 |
| B | B1 | 3 |
| C | C1 | 4 |
| C | C2 | -10 |
| C | C3 | 5 |
i created measuer: result= calculate(sum(value),category="A" or "C")
=17
i want the result value 17 is only for categroy A like in format:
| Category | Sub Category | value | result |
| A | A1 | 5 | 17 |
| A | A1 | 10 | 17 |
| A | A2 | 3 | 17 |
| B | B1 | 6 | 0 |
| B | B1 | 3 | 0 |
| C | C1 | 4 | 0 |
| C | C2 | -10 | 0 |
| C | C3 | 5 | 0 |
| Total | 26 | 17 |
how can i achieve using dax
Hi jyaul786 ,
I suggest you to try code as below to create a calculated column.
Result = VAR CategoryA = SUMX ( FILTER ( 'Table', 'Table'[Category] in {"A","C"} ), 'Table'[value] ) RETURN IF ( [Category] = "A", CategoryA, 0 )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi jyaul786 ,
I suggest you to try code as below to create a calculated column.
Result = VAR CategoryA = SUMX ( FILTER ( 'Table', 'Table'[Category] in {"A","C"} ), 'Table'[value] ) RETURN IF ( [Category] = "A", CategoryA, 0 )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To achieve the desired result in Power BI using DAX, you can create a calculated column or a measure that calculates the sum of "value" for Category A and C and then use this result to create a new column for the "result" values in your table. Here's how you can do it step by step:
- Create a measure to calculate the sum of "value" for Category A and C:
TotalValue = CALCULATE(SUM('YourTable'[value]), 'YourTable'[Category] IN {"A", "C"})
In the above DAX formula, 'YourTable' should be replaced with the actual name of your table.
- Now, you can create a new calculated column to display the "result" values:
Result = IF('YourTable'[Category] IN {"A", "C"}, [TotalValue], 0)
This calculated column checks if the Category is either "A" or "C" and if so, it assigns the value from the TotalValue measure; otherwise, it assigns 0.
- Add this "Result" column to your table visualization, and it will display the "result" values as you described.
Your table will now look like this:
Category Sub Category value result
A A1 5 17
A A1 10 17
A A2 3 17
B B1 6 0
B B1 3 0
C C1 4 17
C C2 -10 17
C C3 5 17
Total 26 17Make sure to replace 'YourTable' with the actual name of your table in your Power BI data model. This approach uses DAX measures and calculated columns to achieve the desired result in your table visualization.
i tried with your measure and column but my expected result is not matching with your meauser and column. expected result:
Category Sub Category value result A A1 5 17 A A1 10 17 A A2 3 17 B B1 6 0 B B1 3 0 C C1 4 0 C C2 -10 0 C C3 5 0 Total 26 17 I apologize for the misunderstanding. To achieve the expected result where the "result" column displays the sum of "value" only for "Category" A and 0 for other categories, you can use the following DAX measure:
Result =
VAR CategoryA =
SUMX (
FILTER ( YourTableName, YourTableName[Category] = "A" ),
YourTableName[value]
)
RETURN
IF ( YourTableName[Category] = "A", CategoryA, 0 )Replace YourTableName with the name of your table where the data is stored.
This measure calculates the sum of "value" for "Category" A and stores it in the CategoryA variable. Then, it uses an IF statement to check if the current row's "Category" is "A." If it is, it returns the value stored in CategoryA; otherwise, it returns 0.
With this measure, you should get the expected result as you described in your question.
To achieve the desired result using DAX (Data Analysis Expressions) in Power BI or another similar tool, you can create a calculated column that calculates the result for each row based on your condition. Here's a step-by-step guide on how to do this:
Open your Power BI Desktop or similar tool.
In the Fields pane, select the table where your data is located.
Click on "Modeling" in the top menu bar.
Select "New Column" to create a new calculated column.
Name the new column, for example, "Result."
Use the following DAX formula to calculate the result based on your condition:
Result =
VAR SelectedCategories = {"A", "C"}
RETURN
IF(
SUMMARIZE(FILTER(YourTableName, YourTableName[Category] IN SelectedCategories), YourTableName[Category]),
CALCULATE(SUM(YourTableName[value]))
)Replace YourTableName with the name of your table where the data is stored.
This formula does the following:
It first defines a variable SelectedCategories that contains the categories you want to include in the calculation ("A" and "C" in this case).
Then, it uses the FILTER function to filter the table to include only rows where the Category is in the selected categories.
Next, it uses the SUMMARIZE function to create a table with distinct Category values based on the filtered data.
Finally, it calculates the sum of the "value" column for the selected categories using the CALCULATE function.
- Click the checkmark (√) to accept the formula.
Now, your table should have a new "Result" column that contains the calculated results for each row based on the condition. You can display this column in your table visual to see the results as you described in your example.
Note: Make sure to replace "YourTableName" with the actual name of your table in the DAX formula.
Also Try This:
I see that you want the "Result" column to be calculated differently. Based on your updated requirement, you want the "Result" column to be 17 for all rows in Category A and 0 for all other rows. In this case, you should use a different DAX formula for the "Result" column. Here's the updated DAX formula:
Result =
IF(
YourTableName[Category] = "A",
CALCULATE(SUM(YourTableName[value]), FILTER(YourTableName, YourTableName[Category] = "A")),
0
)This formula does the following:
It checks if the Category is "A" for each row using the IF statement.
If the Category is "A," it calculates the sum of the "value" column for rows where the Category is "A" using the CALCULATE function with a FILTER condition.
If the Category is not "A," it assigns a value of 0 to the "Result" column for that row.
Now, your "Result" column should be calculated as 17 for rows with Category "A" and 0 for all other rows, as you've described in your expected result.
If I answered your question, please mark my post as solution, Appreciate your Kudos.
