Forum Discussion

Responsive Resident

4 years ago

Solved

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, (...

tamerj1
4 years ago
spandy34
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

tamerj1

Community Champion

4 years ago

Hi spandy34

what are your measures? Is Everything in one table?

spandy34

Responsive Resident

4 years ago

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

tamerj1
Community Champion
4 years ago
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!
- tamerj1
  Community Champion
  4 years ago
  Of course the grand total is another subject. Let's first check if this works.
- spandy34
  Responsive Resident
  4 years ago
  Here is the measure I have created based on your suggestion and inserted the relevant measures but get an error
  
  Amount Recovered =
  VAR A.CurrentMonth = 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 ) )
  
  VAR A.YearTD = CALCULATE (
  COUNTROWS ( 'Main Claim Data' ),
  FILTER (
  'Main Claim Data',
  'Main Claim Data'[ClassOfBusinessCode] = "OT"
  && CONTAINSSTRINGEXACT ( [PolicyCode], "REC" )),
  DATESYTD('Fin_Calendar'[Date]))
  
  VAR A.Total = --insert your measure for Total - A. Uninsured Loss Recoveries
  CALCULATE (
  COUNTROWS ( 'Main Claim Data' ),
  FILTER (
  'Main Claim Data',
  'Main Claim Data'[ClassOfBusinessCode] = "OT"
  && CONTAINSSTRINGEXACT ( [PolicyCode], "REC" )),
  DATESYTD('Fin_Calendar'[Date]))
  
  VAR B.CurrentMonth = --insert your measure for Current Month - B. Motor Recoveries
  Current Month Succ Rec Motor Recoveries =
  var _min = eomonth(today(),-1)+1
  var _max = eomonth(today(),0) //today()
  return
  CALCULATE([Recoveries No Successful Recoveries MV], FILTER(ALL('Main Claim Data'),'Main Claim Data'[NotificationDate] >= _min && 'Main Claim Data'[NotificationDate] <=_max ) )
  
  VAR B.YearTD = --insert your measure for YTD - B. Motor Recoveries
  YTD Recoveries No Successful Recoveries MV =
  CALCULATE (
  COUNTROWS ( 'Main Claim Data' ),
  FILTER (
  'Main Claim Data',
  'Main Claim Data'[ClassOfBusinessCode] = "MV"
  && CONTAINSSTRINGEXACT ( [PolicyCode], "MV" )),
  DATESYTD('Fin_Calendar'[Date]))
  
  VAR B.Total = --insert your measure for Total - B. Motor Recoveries
  Total Recoveries No Successful Recoveries MV =
  CALCULATE (
  COUNTROWS ( 'Main Claim Data' ),
  FILTER (
  'Main Claim Data',
  'Main Claim Data'[ClassOfBusinessCode] = "MV"
  && CONTAINSSTRINGEXACT ( [PolicyCode], "MV" )),
  DATESYTD('Fin_Calendar'[Date]))
  RETURN
  SWITCH (
  TRUE,
  SELECTEDVALUE ( 'Recoveries Columns'[Value1] ) = "Current Month"
  && SELECTEDVALUE ( 'Recoveries Row'[Value] ) = "A. Uninsured Loss Recoveries", A.CurrentMonth,
  SELECTEDVALUE ( 'Recoveries Columns'[Value1] ) = "Current Month"
  && SELECTEDVALUE ( 'Recoveries Row'[Value] ) = "B. Motor Recoveries", B.CurrentMonth,
  SELECTEDVALUE ( 'Recoveries Columns'[Value1] ) = "YTD"
  && SELECTEDVALUE ( 'Recoveries Row'[Value] ) = "A. Uninsured Loss Recoveries", A.YearTD,
  SELECTEDVALUE ( 'Recoveries Columns'[Value1] ) = "YTD"
  && SELECTEDVALUE ( 'Recoveries Row'[Value] ) = "B. Motor Recoveries", B.YearTD,
  SELECTEDVALUE ( 'Recoveries Columns'[Value1] ) = "Total"
  && SELECTEDVALUE ( 'Recoveries Row'[Value] ) = "A. Uninsured Loss Recoveries", A.Total,
  SELECTEDVALUE ( 'Recoveries Columns'[Value1] ) = "Total"
  && SELECTEDVALUE ( 'Recoveries Row'[Value] ) = "B. Motor Recoveries", B.Total
  )
  - tamerj1
    Community Champion
    4 years ago
    spandy34
    First define all measures seperately the do like this:
    VAR B.YearTD = [YTD Recoveries No Successful Recoveries MV]

Forum Discussion

Visualisations Advice

All measure are from the same table named Main Claim Data

Uninsured Loss Recoveries Column Measures

Related content

Visualisation Advice

Multiple DAX for WoW, YoY, MoM Advice

Matrix Layout Advice

Workspace advice for ML tasks

Power BI Python Visualisations being depreciated?