Basic Question? Divide for average costs per month

[Talvien]

Hi,

I'm very new and have a realy basic problem. I have 5 projects and a sum of costs over a few month for each of them.

Now in the table it seems to be very easy to calculate the average costs for each month through dividing the Costs with the Counter of months. But in a measure this doesn't work at all.

I tried following Formula:

Measure = DIVIDE(SUM('Detailed Usage (2)'[Costs]); COUNT('Time'[Month])).

So my question is, how can I get the avaerage costs per month for each project to do a forecast for the rest of the year?

Thanks in advance!

[Vvelarde · Accepted solution]

If Time.Month are string the Function Count not work because only work with numbers. Try with CountA

[v-qiuyu-msft · Accepted solution]

Hi @Talvien,

In your scenario, as 'Detailed Usage' table contains project, month and costs columns, you can calculate average costs for each project per month, you can create a measure like that:

Measure= sum('Detailed Usage'[Costs]/DISTINCTCOUNT('Detailed Usage'[Month])

Then you can use table visual to display [Project] and Measure values. See:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Vvelarde]

Exist a relationship between these tables..Detailed Usage and Time? and if exist what is the key..

[Talvien]

Yes, there are relations. Time.ID is related to Detailed Usage (2).Month. Time.Month are Strings; the names of the Months instead of numbers.

[Vvelarde · Accepted solution]

If Time.Month are string the Function Count not work because only work with numbers. Try with CountA