Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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]
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
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])
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
Hi Vimal , Here is the formula I am writing
Here are the values I am getting
@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])
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |