Sum only negatives values from measure

[Morrison]

HI, I have a pivot table and for each year month I insert the YoY value. In the total I would like to include only the total of negative values. Can you help me please. Thx

[Morrison]

Thank you, but I can't create new columns as I'm working on a cube.

[123abc]

If you're working with a cube in Power BI and cannot create new columns, you can achieve this by creating a calculated member directly within your cube using MDX (Multidimensional Expressions). Calculated members allow you to define custom calculations on the cube data.

Assuming your measure is named [YoY Value], you can create a calculated member that sums only the negative values. Here's an example of how you can do it:

CREATE MEMBER CURRENTCUBE.[Measures].[NegativeTotal]
AS
SUM(
FILTER(
[YourDimension].[YearMonth].MEMBERS,
[Measures].[YoY Value] < 0
),
[Measures].[YoY Value]
),
VISIBLE = 1;

Replace [YourDimension] with the actual name of your time dimension, [YearMonth] with the actual hierarchy level you are using (e.g., [Year].[Month] or similar), and [Measures].[YoY Value] with the actual name of your YoY measure.

After adding this calculated member to your cube, you should be able to use it in your reports or pivot tables to display the sum of negative YoY values.

Keep in mind that the exact syntax may vary depending on the structure of your cube and the version of your analysis services. Make sure to adapt the code accordingly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc · Accepted solution]

It seems like you want to sum only the negative values in a column in Power BI. Here's a general approach you can follow:

1. Create a Calculated Column: In Power BI, you can create a new calculated column using the DAX (Data Analysis Expressions) language. Assuming your column is named YoYValue, you can create a new column with the following formula:

NegativeSum = IF([YoYValue] < 0, [YoYValue], 0)

1. This formula checks if the YoYValue is negative. If it is, it returns the value; otherwise, it returns 0.

2. Summarize the Negative Values: After creating the new column, you can use it to calculate the sum of the negative values. You can do this by creating a measure. Create a new measure with the following formula:

TotalNegativeSum = SUM('YourTableName'[NegativeSum])

1. Replace 'YourTableName' with the actual name of your table.

2. Display the Measure in Your Report: Drag the TotalNegativeSum measure into your report to display the total of negative values.

Remember that this is a general approach, and the specifics might vary based on your exact data model and requirements. If you encounter any issues or have a different structure in your data, you may need to adapt the solution accordingly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.