Create a Measure to sum only positive values of another measure

[MichaelStrauss]

Hi! I am struggling with creating a dax to sum the results returned by a measure considering only its positive values

So I have few tables with the following columns:

Fact_Sales: [Material] [Unit] [Operational_Cat] [Store] [Sales]

dim_Material: [Material] [Operational_Cat]

dim_Operational_Cat: [Operational_Cat]

dim_Stores: [Store]

dim_benchmark_Store: [Store]

dim_unit: [Unit]

And the modeling is as follows:

I created a few simple measures for the sum of sales:

Store_sales = CALCULATE(SUM(fact_sales[Sales]))  -- sales of the store

Benchmark_store_sales = CALCULATE(Store_sales,ALL(dim_Store),  USERELATIONSHIP ... -- benchmark store sales

And measures for the material share from the total sales of each operationl category (sales / total sales per operational category):

store_material_share = DIVIDE([Store_sales],CALCULATE(Store_sales,ALLSELECTED(dim_unit[unit]),ALLSELECTED(dim_Material[Material])))

Same I did for the benchmark

(did the same for the benchmark store)

Now I'm creating a measure to calculate the potential revenue my store could get if the material market share was same as the benchmark store:

Potential Revenue = (DIVIDE([benchmark_material_share] ,[store_material share]) - 1) * [Store_sales]

Results are correct and I'm later filtering my matrix to only show positive values (because negative value has no meaning to me)

The matrix is as follows:

Problem is that for sub-totals I do not want the measure to sum the negative values .. I want the sub-total per company and per operational category (possibly with HASONEVALUE) to only sum the positive values returned by the POTENTIAL REVENUE measure, but I can't filter the measure results to only consider positive value (I can filter the matrix but the sub-total still sums the negative values)

Help will be appreciated!! This is a common problem that I can't solve

[MichaelStrauss]

Someone who gives lectures on power bi helped me to find a solution

He used a var inside a return statement to set a fixed denominator, first time I see a loop within a return statement

[v-rzhou-msft]

Hi @MichaelStrauss , 

I suggest you to create a new measure based on [Portential Revenue] and add some filter on it. I think you use [Operational_Cat]/[Material]/[Unit] as three hierachy levels in matrix.

SUM WITHOUT NEGATIVE =
SUMX (
    FILTER (
        SUMMARIZE (
            'Fact_Sales',
            'Fact_Sales'[Operational Cat],
            'Fact_Sales'[Unit],
            'Fact_Sales'[Material]
        ),
        [Potential Revenue] >= 0
    ),
    [Potential Revenue]
)

Best Regards,
Rico Zhou

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

[MichaelStrauss]

Hi @v-rzhou-msft 

Thank you for your answer

I've tried your solution but unfortunately it does not do as requested

Instead of summing just positive values what it does is calculating the measure Potential_Revenue with the other sub-total measure values. Meaning that if the calculation of the sub-total row level is less then 0 it returns blank, and if it's above 0 it just uses the row context and rather than sum up the previous hierarchy level values