Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.
PBI Live connect to AAS (dynamic columns in matrix) ?
Hi all, Iam in trouble with creating matrix in PBI which will dynamically change columns/rows based on selection from slicer. Iam using live connect to Azure analysis tabular model actually and trying to do following solution: static data table for colum names, measure to identify change, row_selector:=sum('Value selector row'[Order]) calculated column "rows" = var x1=IF([row_selector]>39,1,[row_selector]) var x2= CALCULATE(VALUES('Value selector row'[Name]),FILTER('Value selector row','Value selector row'[Order]=x1)) return switch( x2, "Stredisko",RELATED('xy1'[x)]), "Prevádzka",RELATED('xy2'[y)]), "Pokladňa",RELATED('xy3'[z]), "N/A" ) In AAS visual studio is everything working fine, but after deploy to PBI row value is not changing either when underlying measure changes correctly. Is there some way to refresh calculated colum when PBI (live connected) slicer selection changes ? Thank you
No enough memory to complete this operation when loadin measure into matrix
Hi Everyone, im working on power bi report where i need to multiply "Losses 3 Years Rate" measure values based on Index Year in matrix below is the formula of "Losses 3 Years Rate": Losses 3 Years Rate = IFERROR(DIVIDE( CALCULATE ( [Incurred Claims], FILTER ( ALL( LineReport[Index Year],LineReport[UW Year]), LineReport[Index Year] <= MAX ( LineReport[Index Year]) && LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year]) ) ) , if( SELECTEDVALUE(LineReport[Index Year])=1, CALCULATE( [Incurred Claims], FILTER ( ALL( LineReport[Index Year],LineReport[UW Year]), LineReport[Index Year] <= MAX ( LineReport[Index Year]) && LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year]) ) ), CALCULATE ( [Incurred Claims], FILTER ( ALL( LineReport[Index Year],LineReport[UW Year]), LineReport[Index Year] <= MAX ( LineReport[Index Year])-1 && LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year]) ) ) ),0) it is properly generated as per the below screen shot: now i need to calculate Developement Factor which is the "Losses 3 years Rate" for index year multiply by the others for example: Dev Fac for index 1 = Rate1 * rate2*rate3*rate4*rate5*rate6*rate7*rate8 Dev Fac for index 2 = rate2*rate3*rate4*rate5*rate6*rate7*rate8 Dev Fac for index 3 = rate3*rate4*rate5*rate6*rate7*rate8 Dev Fac for index 4 = rate4*rate5*rate6*rate7*rate8 Dev Fac for index 5 = rate5*rate6*rate7*rate8 Dev Fac for index 6 = rate6*rate7*rate8 Dev Fac for index 7 = rate7*rate8 Dev Fac for index 8 = rate8 Im using the below formula: Cape Cod IL 3 Years Rate = var indexyear = SELECTEDVALUE(LineReport[Index Year]) return EXP( SUMX( FILTER(ALL(LineReport), LineReport[Index Year]<=MAX(LineReport[Index Year]) && LineReport[Index Year]>= indexyear) , LN([Losses 3 Years Rate]) ) ) however the matrix is keep loading and throw a memrory error: below is the formula i used in Matrix Value: Rate Calculation = VAR Val = SWITCH(SELECTEDVALUE('Chain Ladder'[Group]), "Premium 3 Years",[Premium 3 Years Rate], "Premium 5 Years",[Premium 5 Years Rate], "Premium 7 Years",[Premium 7 Years Rate], "Premium All Years",[Premium All Years Rate], "Premium Average",[Premium Average Years Rate], "Losses 3 Years", [Losses 3 Years Rate], "Losses 5 Years",[Losses 5 Years Rate], "Losses 7 Years",[Losses 7 Years Rate], "Losses All Years",[Losses All Years Rate], "Losses Average",[Losses Average Years Rate], "Development Factor",'Cape Cod'[Cape Cod IL 3 Years Rate] ) RETURN FORMAT(Val, "0.00000") appreciate your assistance since i cant go any further with this. Best regards, Georges
