topic Re: how to get the Proportioned sum using DAX in DAX Commands and Tips

how to get the Proportioned sum using DAX

Anonymous — Wed, 13 Nov 2019 02:54:34 GMT

Hi All,

I need help to get the Proportioned sum of the values using DAX

Here is data

Category	Value	Proportion
A	50	30%
B	4	40%
C	7	15%
D	6	15%
E	200

Now I need a new column with the values multiplied by the Proportion of given value of E category value and the existing value in Simple A= 50+ 30%(200), B= 4+40%(200), c=7+15%(200) etc.

How can i write the Dax formula for this?

Please Help.

Re: how to get the Proportioned sum using DAX

Anonymous — Wed, 13 Nov 2019 04:16:21 GMT

Hi Anonymous Please use below code. In case you want measure use first one and in case you want calculated column use below one

Measure = VAR E_category = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Category]="E")) RETURN SUMX('Table',('Table'[Proportion]*E_category)+'Table'[Value]) Column = VAR E_category = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Category]="E")) RETURN ('Table'[Proportion]*E_category)+'Table'[Value]

Re: how to get the Proportioned sum using DAX

Anonymous — Wed, 13 Nov 2019 20:27:51 GMT

Hi Vimal,

Thanks for the Formula. The Formula is Working Fine with the Measure, Returning Expected results But when I use the Filter, It is not returning data with the filter Used, For example, see the data

Week	Category	Value	Proportion
Wk1	A	50	30%
Wk1	B	4	40%
Wk1	C	7	15%
Wk1	D	6	15%
Wk1	E	200
Wk2	A	22	30%
Wk2	B	32	40%
Wk2	C	52	15%
Wk2	D	6	15%
Wk2	E	500

I need thew Proportioned number for Each week, But it is taking on the total sum. Please explain.

Thanks

Re: how to get the Proportioned sum using DAX

Anonymous — Thu, 14 Nov 2019 05:05:39 GMT

Hi Anonymous you mean E category value should be calculated every week. Use this measure

Measure = VAR E_category = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table',Data[Week]),'Table'[Category]="E")) RETURN SUMX('Table',('Table'[Proportion]*E_category)+'Table'[Value])

Re: how to get the Proportioned sum using DAX

Anonymous — Thu, 14 Nov 2019 07:38:35 GMT

Thanks Vimal, Appreciate your Help.

I am able to write the formula correctly , but the values I am getting is way higher than what was expected. I am asuming may be because of the extra columns in the data set which is duplicating the values. Any thoughts

Re: how to get the Proportioned sum using DAX

Anonymous — Thu, 14 Nov 2019 07:50:24 GMT

Hi Vimal , Here is the formula I am writing

Proportned Hours = var E_Category = CALCULATE(sum('Doller values'[Hours]),FILTER(ALLEXCEPT('Doller values','Doller values'[Week Ending].[Date]),'Doller values'[Merge Programme]="I&A Department")) RETURN Sumx('Doller values',('Doller values'[Financials.I&A Reallocation Rates]*E_Category)+'Doller values'[Hours])

Here are the values I am getting

Re: how to get the Proportioned sum using DAX

Anonymous — Thu, 14 Nov 2019 09:01:45 GMT

Anonymous Please remove .[Date] from [Week Ending].[Date] and see if values are coming as expected

Proportned Hours = var E_Category = CALCULATE(sum('Doller values'[Hours]),FILTER(ALLEXCEPT('Doller values','Doller values'[Week Ending]),'Doller values'[Merge Programme]="I&A Department")) RETURN Sumx('Doller values',('Doller values'[Financials.I&A Reallocation Rates]*E_Category)+'Doller values'[Hours])

Re: how to get the Proportioned sum using DAX

Anonymous — Sun, 17 Nov 2019 20:43:07 GMT

HI Anonymous ,

I am able to get the Correct No.of Hours Based on your Formula, But when I add the Running Total for the Calculated Column , I am Getting Wrong Result . See the Image below.

Here is the Formula I used to calculate Running Total

Hours Proposed running total in Week ending =

CALCULATE(

[Hours Proposed],

FILTER(

ALLSELECTED('Final Template'[Week ending]),

ISONORAFTER('Final Template'[Week ending], MAX('Final Template'[Week ending]), DESC)

)

Please Help!

Thanks

Re: how to get the Proportioned sum using DAX

Anonymous — Mon, 18 Nov 2019 04:16:53 GMT

Anonymous
In case you want ot get program wise running total

Measure 2 = CALCULATE([Hours Proposed],FILTER(ALLEXCEPT(Test,Test[Program Name]),Test[WeekEnding]<=MAX(Test[WeekEnding])))

use below measure