how to get the Proportioned sum using DAX

[Anonymous]

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. 

[Anonymous · Accepted solution]

@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])

[Anonymous]

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]

[Anonymous]

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

[Anonymous]

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])

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

@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])

[Anonymous]

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

[Anonymous · Accepted solution]

@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