Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Sofinobi
Helper IV
Helper IV

calculate the diffrence

hi all,
please, i have a measure to calculate the diffrence between the Sales and Target (Diff = Sales - Target), when i visualize this in a Table by Client, so i have positive and negative results (it depends on Sales by Client),

i would like to calculate the Sum of Diff only for the negatives results.
diff2.png
thank you so much

2 ACCEPTED SOLUTIONS
talespin
Solution Sage
Solution Sage

Hi @Sofinobi ,

 

Using below to simulate your use case. This will give you sum of negative numbers, you need to replace summarize with your visual level filters if any or otherwise with table column that you want to group by(Example difference for products here).

Replace it with your measure that returns the difference.([MQuantity]- [MCustomQuantity] )

 

MDiff =
SUMX(
    SUMMARIZE(DimProduct, DimProduct[EnglishProductName]),
    VAR Diff = [MQuantity]- [MCustomQuantity]    
    RETURN
    IF(Diff < 0, Diff, 0)
)

 

talespin_0-1706671848591.png

 

View solution in original post

v-huijiey-msft
Community Support
Community Support

Hi @Sofinobi ,

 

Hope all is going well.

 

From your description, you want to create a new measure in Power BI that will sum only the negative differences between Sales and Target for each client.

 

Please follow these steps:

 

1. First, here is my sample data.

vhuijieymsft_0-1706689416881.png

 

2. Create a measure value. The specific syntax is as follows.

 

Diff =
VAR a = MAX([Sales])-MAX([Target])
RETURN
IF(a < 0, a, 0)

 

 

The visual object presented at this time is as follows.

vhuijieymsft_1-1706689439475.png

 

3. If you want to display only negative numbers in the visual, you can format it in Filter as follows.

 

The visual objects at this time are as follows.

vhuijieymsft_2-1706689451233.png

 

Your needs should now be met. If you encounter any problems, please feel free to contact me. Thank you for your patience and cooperation.

 

Warm Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

6 REPLIES 6
v-huijiey-msft
Community Support
Community Support

Hi @Sofinobi ,

 

Hope all is going well.

 

From your description, you want to create a new measure in Power BI that will sum only the negative differences between Sales and Target for each client.

 

Please follow these steps:

 

1. First, here is my sample data.

vhuijieymsft_0-1706689416881.png

 

2. Create a measure value. The specific syntax is as follows.

 

Diff =
VAR a = MAX([Sales])-MAX([Target])
RETURN
IF(a < 0, a, 0)

 

 

The visual object presented at this time is as follows.

vhuijieymsft_1-1706689439475.png

 

3. If you want to display only negative numbers in the visual, you can format it in Filter as follows.

 

The visual objects at this time are as follows.

vhuijieymsft_2-1706689451233.png

 

Your needs should now be met. If you encounter any problems, please feel free to contact me. Thank you for your patience and cooperation.

 

Warm Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

thank you @v-huijiey-msft  it works perfectly, thats exactely what i'm looking for.

thank you again

talespin
Solution Sage
Solution Sage

Hi @Sofinobi ,

 

Using below to simulate your use case. This will give you sum of negative numbers, you need to replace summarize with your visual level filters if any or otherwise with table column that you want to group by(Example difference for products here).

Replace it with your measure that returns the difference.([MQuantity]- [MCustomQuantity] )

 

MDiff =
SUMX(
    SUMMARIZE(DimProduct, DimProduct[EnglishProductName]),
    VAR Diff = [MQuantity]- [MCustomQuantity]    
    RETURN
    IF(Diff < 0, Diff, 0)
)

 

talespin_0-1706671848591.png

 

hi @talespin  
thank you so much, it works perfectly.

thank you again my friend

You're welcome

Greg_Deckler
Super User
Super User

@Sofinobi This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors