Forum Discussion
Creating a Metric that will Calculate Total Average Daily Volume fora Given Period
Hello! I have data that looks somewhat like the below table. What I need to do is create a metric that will calculate the Average Daily Volume for a given period of time that can be changed with filters, however each country code can have it's own number of business days and so we want to calculate the ADV by Country to get to the actual number.
So in the example below, I have to find the US's YTD ADV and add it to Canada's YTD ADV to get the actual YTD ADV for all countries. How would I go about doing this in Power BI with SUMX - or another function?
Hi,
PBI file attached.
Hope this helps.
8 Replies
- cengizhanarslan
Super User
Please try the formula below:
Total ADV := SUMX ( VALUES ( Fact[Country] ), DIVIDE ( CALCULATE ( SUM ( Fact[Volume] ) ), CALCULATE ( SUM ( Fact[Bus Days] ) ) ) ) Thank you! I did try this on my data source, but it only yields about 1% of the total that it should be
Hi rhalper ,
The issue you're encountering is most likely due to a grain mismatch rather than a problem with SUMX itself. In your model, volume is already aggregated at the weekly level, but the calculation for business days is being performed at a more detailed level. This causes the denominator to be larger than intended, resulting in a value that's only about one percent of what you expect.
To resolve this, ensure that both volume and business days are evaluated at the same level before dividing. Since your table records the correct number of business days per country and week, you should avoid summing or counting these values across rows. Instead, use the business days value once per country and week, calculate the average daily volume at that level, and then aggregate the results by country.
A commonly effective measure for this scenario is:
Total ADV =
SUMX (
VALUES ( Fact[Country] ),
AVERAGEX (
VALUES ( Fact[Week] ),
DIVIDE (
CALCULATE ( SUM ( Fact[Volume] ) ),
CALCULATE ( MAX ( Fact[Bus Days] ) )
)
)
)This method ensures alignment by calculating the weekly average daily volume for each country using the correct business days value, and then summing those results. This approach prevents double counting of business days, so the total will scale appropriately with YTD or other date filters, providing values much closer to your expectations.
Thank you.Hi rhalper ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.
