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.
Store Name | Postal Code | Sales | Postal Code Sales |
Store A | 06385 | 100 | 1,000 |
Store B | 06385 | 200 | 1,000 |
Store C | 90210 | 300 | 3,000 |
Store D | 90210 | 300 | 3,000 |
TOTAL | 900 | 4,000 |
Solved! Go to Solution.
Hi @Mainer04401 ,
try this
SUMX (
SUMMARIZE (
'Table',
'Table'[Postal Code],
'Table'[Product],
'Table'[Week],
"@AVG", CALCULATE (
AVERAGE ( 'Table'[Trading Area Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Postal Code], 'Table'[Product], 'Table'[Week] )
)
),
[@AVG]
)
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
The formula below APPEARS to do what I need but if there is a cleaner, more efficient way, I would love to hear it!
sumx(values(Table[Postal Code]),
sumx(values(Table[Product]),
sumx(values(Table[Week],
calculate(average(Table[Trading Area Sales),
allexcept(Table,Table[Postal Code],Table[Product],Table[Week])))
Essentially I need PowerBI to sum the average of each product's sales, during each week, at the postal code level in order to remove duplicates at the postal code level.
Hi @Mainer04401 ,
try this
SUMX (
SUMMARIZE (
'Table',
'Table'[Postal Code],
'Table'[Product],
'Table'[Week],
"@AVG", CALCULATE (
AVERAGE ( 'Table'[Trading Area Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Postal Code], 'Table'[Product], 'Table'[Week] )
)
),
[@AVG]
)
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
@mwegener- Thank you! That worked and that formula loads much quicker than the formula I used so it must be much more efficient!
Refer to this
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |