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.
Hello everyone,
(Basically for PRICE I want the AVERAGE, but for the other 2 measures Quantity and Revenue, I want them to stay as SUM). Is that possible?
Solved! Go to Solution.
Hi @josiasbr ,
You can try to use following measure if it suitable for your requirement:
Measure = IF ( ISINSCOPE ( Table1[Month] ) && ISINSCOPE ( Table1[Version] ), SUM ( Table1[Value] ), AVERAGE ( Table1[Value] ) )
Regards,
Xiaoxin Sheng
HI @josiasbr ,
You can add if statement to check current row content level and write a formula to summarize total level.
If you are confused on coding formula, can you please share some sample data for test?
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Thank you for your answer!
My data is this :
Service | Version | Month | Month Nr | Attribute | Value |
Delivery | Actual | Jan | 1 | Quantity | 332 |
Delivery | Actual | Jan | 1 | Price | 728,51 |
Delivery | Actual | Jan | 1 | Revenue | 242 |
Delivery | Budget | Jan | 1 | Quantity | 347 |
Delivery | Budget | Jan | 1 | Price | 819,5 |
Delivery | Budget | Jan | 1 | Revenue | 284 |
Delivery | Actual | Feb | 2 | Quantity | 317 |
Delivery | Actual | Feb | 2 | Price | 740,22 |
Delivery | Actual | Feb | 2 | Revenue | 235 |
Delivery | Budget | Feb | 2 | Quantity | 330 |
Delivery | Budget | Feb | 2 | Price | 819,5 |
Delivery | Budget | Feb | 2 | Revenue | 270 |
Delivery | Actual | Mar | 3 | Quantity | 296 |
Delivery | Actual | Mar | 3 | Price | 776,09 |
Delivery | Actual | Mar | 3 | Revenue | 230 |
Delivery | Budget | Mar | 3 | Quantity | 393 |
Delivery | Budget | Mar | 3 | Price | 819,5 |
Delivery | Budget | Mar | 3 | Revenue | 322 |
Delivery | Actual | Apr | 4 | Quantity | 294 |
Delivery | Actual | Apr | 4 | Price | 766,9 |
Delivery | Actual | Apr | 4 | Revenue | 225 |
Delivery | Budget | Apr | 4 | Quantity | 339 |
Delivery | Budget | Apr | 4 | Price | 819,5 |
Delivery | Budget | Apr | 4 | Revenue | 278 |
Assembly | Actual | Jan | 1 | Quantity | 1122 |
Assembly | Actual | Jan | 1 | Price | 247,85 |
Assembly | Actual | Jan | 1 | Revenue | 278 |
Assembly | Budget | Jan | 1 | Quantity | 1075 |
Assembly | Budget | Jan | 1 | Price | 256,29 |
Assembly | Budget | Jan | 1 | Revenue | 276 |
Assembly | Actual | Feb | 2 | Quantity | 1009 |
Assembly | Actual | Feb | 2 | Price | 248,06 |
Assembly | Actual | Feb | 2 | Revenue | 250 |
Assembly | Budget | Feb | 2 | Quantity | 1000 |
Assembly | Budget | Feb | 2 | Price | 256,29 |
Assembly | Budget | Feb | 2 | Revenue | 256 |
Assembly | Actual | Mar | 3 | Quantity | 1032 |
Assembly | Actual | Mar | 3 | Price | 248,58 |
Assembly | Actual | Mar | 3 | Revenue | 257 |
Assembly | Budget | Mar | 3 | Quantity | 1035 |
Assembly | Budget | Mar | 3 | Price | 256,29 |
Assembly | Budget | Mar | 3 | Revenue | 265 |
Assembly | Actual | Apr | 4 | Quantity | 931 |
Assembly | Actual | Apr | 4 | Price | 248,83 |
Assembly | Actual | Apr | 4 | Revenue | 232 |
Assembly | Budget | Apr | 4 | Quantity | 992 |
Assembly | Budget | Apr | 4 | Price | 256,29 |
Assembly | Budget | Apr | 4 | Revenue | 254 |
Reparation | Actual | Jan | 1 | Quantity | 328 |
Reparation | Actual | Jan | 1 | Price | 160,91 |
Reparation | Actual | Jan | 1 | Revenue | 53 |
Reparation | Budget | Jan | 1 | Quantity | 319 |
Reparation | Budget | Jan | 1 | Price | 192,41 |
Reparation | Budget | Jan | 1 | Revenue | 61 |
Reparation | Actual | Feb | 2 | Quantity | 296 |
Reparation | Actual | Feb | 2 | Price | 148,66 |
Reparation | Actual | Feb | 2 | Revenue | 44 |
Reparation | Budget | Feb | 2 | Quantity | 298 |
Reparation | Budget | Feb | 2 | Price | 192,41 |
Reparation | Budget | Feb | 2 | Revenue | 57 |
Reparation | Actual | Mar | 3 | Quantity | 292 |
Reparation | Actual | Mar | 3 | Price | 145,9 |
Reparation | Actual | Mar | 3 | Revenue | 43 |
Reparation | Budget | Mar | 3 | Quantity | 320 |
Reparation | Budget | Mar | 3 | Price | 192,41 |
Reparation | Budget | Mar | 3 | Revenue | 62 |
Reparation | Actual | Apr | 4 | Quantity | 298 |
Reparation | Actual | Apr | 4 | Price | 147,54 |
Reparation | Actual | Apr | 4 | Revenue | 44 |
Reparation | Budget | Apr | 4 | Quantity | 298 |
Reparation | Budget | Apr | 4 | Price | 192,41 |
Reparation | Budget | Apr | 4 | Revenue | 57 |
So for every month, I have budget vs Actual for the 3 measures Quantity, Price and Revenue, and for the services "delivery", "assembly" and "reparation"
As I showed in the table above, I would like to see Qty and Revenue as a sum of the 4 months, but in Price I would like to see an average.
What exact formula would I need to type to achieve this?
Hi @josiasbr ,
You can try to use following measure if it suitable for your requirement:
Measure = IF ( ISINSCOPE ( Table1[Month] ) && ISINSCOPE ( Table1[Version] ), SUM ( Table1[Value] ), AVERAGE ( Table1[Value] ) )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |