topic DAX Measure to distribute a total value linearly over months in DAX Commands and Tips

DAX Measure to distribute a total value linearly over months

Cakos — Wed, 04 Mar 2020 09:45:06 GMT

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!

Re: DAX Measure to distribute a total value linearly over months

Anonymous — Wed, 04 Mar 2020 11:44:10 GMT

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.

Re: DAX Measure to distribute a total value linearly over months

Anonymous — Wed, 04 Mar 2020 12:50:30 GMT

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

Re: DAX Measure to distribute a total value linearly over months

Cakos — Thu, 12 Mar 2020 08:37:35 GMT

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?

Re: DAX Measure to distribute a total value linearly over months

TomMartens — Thu, 12 Mar 2020 08:56:20 GMT

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

Re: DAX Measure to distribute a total value linearly over months

Cakos — Thu, 12 Mar 2020 09:23:27 GMT

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?

Re: DAX Measure to distribute a total value linearly over months

Anonymous — Thu, 12 Mar 2020 12:34:47 GMT

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

Re: DAX Measure to distribute a total value linearly over months

Anonymous — Thu, 12 Mar 2020 13:12:56 GMT

// 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

Re: DAX Measure to distribute a total value linearly over months

Cakos — Thu, 12 Mar 2020 14:24:20 GMT

Hey Anonymous ,

and all others,

Many Thanks for your help!