Forum Discussion
Visualisations Advice
Hi
I want to display the following table - with amazing help I have been able to produce measue here displayed as the column rows but I am wanting to know how to get the current month measures, (you have also helped with) year to date and Total measure as a column header and then ultimately Grand Totals.
Im just struggling with the best way to approach this they all appear in a single line if I put them in the Values of a cross tab.
Thank you for your patience with me
MarkLaf tamerj1 goncalogeraldes
You have created a master measure for the number of revoveries in which you grouped 9 measures.
now create another master measure for amount recovered that groups the 9 relared measures the same way as the first one. Copy the same master measure only change its named and the 9 measure names inside.
then do the same for th team fee income.
Then adf the these two master measures into the values of the matrix
26 Replies
All measure are from the same table named Main Claim Data
Relationships
Uninsured Loss Recoveries Column Measures
- No of Successful Recoveries Measure
Recoveries No Successful Recoveries Uninsured Loss =
CALCULATE (
COUNTROWS ( 'Main Claim Data' ),
/* depending on the type of count you need, you might need to use COUNTA() */
FILTER (
'Main Claim Data',
'Main Claim Data'[ClassOfBusinessCode] = "OT"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "REC" ) /* you can use CONTAINSSTRING() if not case sensitive */))
- Amount Recovered Measure
Amount Recovered Uninsured Loss £ = CALCULATE(SUM('Main Claim Data'[Net]),'Main Claim Data'[ClassOfBusinessCode] = "OT" && 'Main Claim Data'[REC_Code_Column] > 0)
- Team Fee Income Measure – (This needs repeating for Motor Recoveries where ClassofBusiness Code = MV and PolicyCode contains MV – haven’t created this yet as I wanted to get Uninsured Loss Recoveries first)
Team Fee Income =
CALCULATE(
SUM( 'Main Claim Data'[Net] ),
KEEPFILTERS( 'Main Claim Data'[ClassOfBusinessCode] = "OT" ),
KEEPFILTERS( CONTAINSSTRING( 'Main Claim Data'[PolicyCode], "REC" ) ),
KEEPFILTERS( CONTAINSSTRING( 'Main Claim Data'[Reference], "ADMIN" ) ))
4) Current Month Measure (This will need repeating for Motor Recoveries)
Current Month Succ Rec uninsured loss =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE([Recoveries No Successful Recoveries Uninsured Loss], FILTER(ALL('Main Claim Data'),'Main Claim Data'[NotificationDate] >= _min && 'Main Claim Data'[NotificationDate] <=_max ) )
5) Year to Date No of Successful Recoveries (Ive done this but Im not sure where it’s the right approach)
YTD Recoveries No Successful Recoveries Uninsured Loss =
CALCULATE (
COUNTROWS ( 'Main Claim Data' ),
FILTER (
'Main Claim Data',
'Main Claim Data'[ClassOfBusinessCode] = "OT"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "REC" )),
DATESYTD('Fin_Calendar'[Date]))
6) Year to Date Measure Amount Recovered (Ive done this but Im not sure where it’s the right approach)
YTD Amount Recovered Uninsured Loss £ = CALCULATE(SUM('Main Claim Data'[Net]),'Main Claim Data'[ClassOfBusinessCode] = "OT" && 'Main Claim Data'[REC_Code_Column] > 0,DATESYTD('Fin_Calendar'[Date]))
7) Year to Date Team Fee Income (Ive done this but Im not sure where it’s the right approach)
YTD Team Fee Income Uninsured Loss £ = CALCULATE(SUM('Main Claim Data'[Net]),'Main Claim Data'[ClassOfBusinessCode] = "OT" && 'Main Claim Data'[REC_Code_Column] > 0 && 'Main Claim Data'[ADMIN_Code_Column] >0,DATESYTD('Fin_Calendar'[Date]))
😎 The final totals final column for each for each I am struggling with as I have done the measure below but I want it to do all records as opposed to Year to Date.
Total Recoveries No Successful Recoveries Uninsured Loss =
CALCULATE (
COUNTROWS ( 'Main Claim Data' ),
FILTER (
'Main Claim Data',
'Main Claim Data'[ClassOfBusinessCode] = "OT"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "REC" )),
DATESYTD('Fin_Calendar'[Date]))
Hi spandy34
I went through all details and I suggest to follow these steps:
1. Shift measure values to rows
2. Create a new table of the desired columns
3. Drag "Value1" into columns
4. Disable column subtotals
5. Create your rows table
6. Drag "Value" into rows
7. Use the SWITCH function to toggle between measures. Example:Amount Recovered = VAR A.CurrentMonth = --insert your measure for Current Month - A. Uninsured Loss Recoveries VAR A.YearTD = --insert your measure for YTD - A. Uninsured Loss Recoveries VAR A.Total = --insert your measure for Total - A. Uninsured Loss Recoveries VAR B.CurrentMonth = --insert your measure for Current Month - B. Motor Recoveries VAR B.YearTD = --insert your measure for YTD - B. Motor Recoveries VAR B.Total = --insert your measure for Total - B. Motor Recoveries RETURN SWITCH ( TRUE, SELECTEDVALUE ( 'Table'[Value1] ) = "Current Month" && SELECTEDVALUE ( 'Table 2'[Value] ) = "A. Uninsured Loss Recoveries", A.CurrentMonth, SELECTEDVALUE ( 'Table'[Value1] ) = "Current Month" && SELECTEDVALUE ( 'Table 2'[Value] ) = "B. Motor Recoveries", B.CurrentMonth, SELECTEDVALUE ( 'Table'[Value1] ) = "YTD" && SELECTEDVALUE ( 'Table 2'[Value] ) = "A. Uninsured Loss Recoveries", A.YearTD, SELECTEDVALUE ( 'Table'[Value1] ) = "YTD" && SELECTEDVALUE ( 'Table 2'[Value] ) = "B. Motor Recoveries", B.YearTD, SELECTEDVALUE ( 'Table'[Value1] ) = "Total" && SELECTEDVALUE ( 'Table 2'[Value] ) = "A. Uninsured Loss Recoveries", A.Total, SELECTEDVALUE ( 'Table'[Value1] ) = "Total" && SELECTEDVALUE ( 'Table 2'[Value] ) = "B. Motor Recoveries", B.Total )Please let me know if this answers your query. Have a nice day!
