Calculating Revenue by day and by each location

[tomerbi]

Hi, 

I'm trying to create a card that shows the sum of revenue per day and per location. The formula I'm using is 

=(SUM(A[cost])/DISTINCTCOUNT(B[date]))/DISTINCCOUNT(C[location])

Short explanation: I have regions and locations, where each region have different number of location and each location has its own id. For example:

Region 1,
  Location 1
  Location 2
Region 2, 
  Location 3
  Location 4
  Location 5

My problem is that some days, some location doesn't make revenues.

I have filter slicers for date, region and location.

When filtering a specific period of dates, the calculation is wrong because it only takes the days and locations that make revenue. The actual calculation should be the total revenue per the number of days that got filtered (not the number of days that only makes revenue) and per total number of locations in the region (not the number of locations that only make revenue). 

I can't hard code for both number of location and days otherwise the other filtering options won't work because I have many different data sources and it's bridged to each other. 

Please let me know if the explanation is unclear. 

Thank you so much in advance

[some_bih]

Hi @tomerbi you need EACH single day, date to count? If yes, you need to create Date / Calendar table in PowerBI. One of way is with CALENDARAUTO function (mark it as Date table), and make relationship. Use this column from Date table as your input in table and check results. Hope this help

https://learn.microsoft.com/en-us/dax/calendarauto-function-dax 

[some_bih]

Hi @tomerbi usually, simple sum for measure AND good model of data (dimensions and fact table properly designed) provide answers to your simple request. 

I suggest to debag issue using simple sum measure for your  cost / revenue. Put date column from your date table in Power BI and location from your dimension. Hope this help

[tomerbi]

Hi @some_bih Thank you very much for your resonse. I have a measure with the fomula above but the real issue is that in the date column is not representing every single day but only the day that the location generated revenue.

For example: Location 1 data table

Date	Location	Revenue
01.02.2023	Location 1	300
02.02.2023	Location 1	500
03.02.2023	Location 1	900
05.02.2023	Location 1	400
06.02.2023	Location 1	600

From the data table above, there is one day that Location 1 doesn't make revenue. When it comes to the calculation of the Location 1 of revenue per day, the result doesn't represent this accurately as this should be /6 days instead of /5 days even there's one day missing.