Forum Discussion
Sum
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 17
Make 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.
