Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

how to get the Proportioned sum using DAX

Hi All, 

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

 

Here is data

CategoryValueProportion
A5030%
B440%
715%
D615%
E200 

 

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. 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

@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 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Not applicable

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

WeekCategoryValueProportion
Wk1A5030%
Wk1B440%
Wk1715%
Wk1D615%
Wk1E200 
Wk2A2230%
Wk2B3240%
Wk25215%
Wk2D615%
Wk2E500 

 

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

 

Thanks

 

Anonymous
Not applicable

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
Not applicable

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
Not applicable

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

 

 
 

Capture.JPG

Anonymous
Not applicable

@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
Not applicable

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.Running Total.JPG

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
Not applicable

@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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors