March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
thank you so much
Solved! Go to Solution.
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] )
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.
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.
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.
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!
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.
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.
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.
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
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] )
You're welcome
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
29 | |
20 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
25 | |
24 | |
22 | |
16 |