DAX Measure to distribute a total value linearly over months

[Cakos]

Hello dear Power BI Community,

I can't seem to find the answer for the following question anywhere.

I have a total value to the amount of 180 as the average of the entire year.

It is my aim to distribute this value linearly over months (180/12).

The result shold look like:

January = 15

February = 15

March = 15

... 

Total = 180

How can I solve the Problem with DAX? Or something else?

Many Thanks!

[Anonymous]

Where do you want to do this? In a visual or in a model table as a calculated column maybe? If in a visual, what should be displayed for granularities other than months?

Best
D

[Cakos]

Thx for your reply!

I want to do this in a visual. 

In this example I created different DAX measures to calculate with different tables in the model.

I want to distribute the total value of Measure 4 (168,23) linearely other months

The outcome should be look like:

Month	Measure 6
January	14,02
February	14,02
...	14,02
December	14,02
total	168,23

Is that possible?

[TomMartens]

Hey @Cakos ,

i guess your Measure 6 should look like this:

Measure 6=
var TotalMeasure4 =
	CALCULATE([Measure 4], ALL('<tablename>'[Monthcolumn]))
return
TotalMeasure4 / 12

Hopefully, this provides what you are looking for.

Regards,

Tom

[Cakos]

Hey @TomMartens

thx for your reply!

If I try your recommended DAX measure it looks not bad but this is not the right outcome:

 

Do you have another idea?

[Anonymous · Accepted solution]

// Say you have a measure
// that calculates correctly
// on the year level but not
// on any other higher granularity.
// Such a measure should be hidden.
// Note the underscore - it indicates
// this is a hidden auxiliary measure
// and should not be used by the end
// user.

[_Measure] = sum( FactTable[Amount] )

// Below is a measure that calculates
// correctly on any level by apportioning
// of the above.

// Assumption is that Calendar (the Date
// table in your model has these levels:
// 1. Year
// 2. Semester
// 2. Quarter
// 3. Month
// 4. Date
// This is a hierarchy and for each level
// you have to apportion the measure
// linearly.

[Measure] =
var __year = values( Calendar[Year] )
var __yearTotal =
	calculate(
		[_Measure],
		__year
	)		
var __periodsInYear =
	switch( true(),
	
		isinscope( Calendar[Date] ),
			calculate(
				countrows( 'Calendar' ),
				__year
			),
				
		isinscope( Calendar[Month] ), 12,
		isinscope( Calendar[Quarter] ), 4,
		isinscope( Calendar[Semester] ), 2,
		isinscope( Calendar[Year] ), 1,
		
		// If nothing is in scope, we'll calculate on the day
		// level. Bear in mind, that the total might be across
		// more than 1 year, so we have to sum up the portions
		// for each year on the day level.
		-1
		
	)
var __apportionByDate =
	// This could be coded in a more performant fashion
	// but it may not be needed.
	sumx(
		values( Calendar[Date] ),
		var __year =
			calculatetable(
				values( Calendar[Year] )
			)
		var __daysInYear =
			calculate(
				countrows( 'Calendar' ),
				__year,
				all( 'Calendar' )
			)
		var __yearTotal =
			calculate(
				[_Measure],
				__year,
				all( 'Calendar' )
			)
		var __dayTotal =
			divide( __yearTotal, __daysInYear )
		return
			__dayTotal
	)
var __result =
	switch( true(),	
		__periodsInYear > 0, divide( __yearTotal, __periodsInYear ),
		__apportionByDate
	)
return
	__result

Try the above. Remember that Calendar is your date dimension (and marked as such in the model) and slicing should only ever be done via dimensions, never directly on a fact table.

Best

D

[Cakos]

Hey @Anonymous ,

and all others,

Many Thanks for your help!

[Anonymous]

Hi there.

Hold on, mate, I'm writing the general measure that will work on any level of the Calendar by linear apportioning of the value from the year.

Best
D

[Anonymous]

add months in table visual and add below measure,

MEasure=sum([value])/count(table[months])

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.