New Measure question and data structure

[troy8]

I have a set of sales data that shows the sales by month for each region for each product.

I want to calculate the number of each product sold for each region per day I was open.  If I write it a calculate formula to do so, it seems I would have to write a seperate calculate formula for each month and product (i.e. 12 different new measures).  Am I thinking about this wrong?  Or is there a way to do it where it knows to do it for each month (i.e. one formula).  Or is there no way to it this way based on the structue of the data?  Do I need to restructure it (i.e. unpivot the months to a column).

Appreciate any feedbackon this as new to Power BI.

Thanks.

[v-sihou-msft · Accepted solution]

@troy8

What does your source table look like?

I assume you should have a fact table which contains the sales per product per month per region. Then you suppose to have another table which includes the opendays associated to each month.

You just nees to build the relationship between two tables on Month columns.

Then you can easily have sales divided by OpenDays.

sales per day = 'Table 2'[Sales]/RELATED('Month'[OpenDays])

Regards,