Forum Discussion

spandy34's avatar
spandy34
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's avatar
    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

spandy34's avatar
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's avatar
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]
  • spandy34's avatar
    spandy34
    Responsive Resident
    4 years ago

    Where do I define them?

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      I mean apparently they are already defined in your model in separate measure right?
      Now just refer to each measure's name inside square brackets 
      VAR xxxx = [measure name]

      • spandy34's avatar
        spandy34
        Responsive Resident
        4 years ago

        Copy the name as opposed to the full measure code?

  • spandy34's avatar
    spandy34
    Responsive Resident
    4 years ago
    Getting an error message
     

     

     
     
     
    Amount Recovered =
    VAR A.CurrentMonth = [Current Month Succ Rec uninsured loss]--insert your measure for Current Month - A. Uninsured Loss Recoveries
    VAR A.YearTD = [YTD Recoveries No Successful Recoveries Uninsured Loss]--insert your measure for YTD - A. Uninsured Loss Recoveries
    VAR A.Total = [Total Recoveries No Successful Recoveries Uninsured Loss]--insert your measure for Total - A. Uninsured Loss Recoveries
    VAR B.CurrentMonth = [Current Month Succ Rec Motor Recoveries]--insert your measure for Current Month - B. Motor Recoveries
    VAR B.YearTD = [YTD Recoveries No Successful Recoveries MV]--insert your measure for YTD - B. Motor Recoveries
    VAR B.Total = [Total Recoveries No Successful Recoveries MV]--insert your measure for Total - B. Motor Recoveries
    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's avatar
      tamerj1
      Community Champion
      4 years ago

      Ok this time my mistake. Just remove the "dot" from the variable name. Fore example instead of :
      A.CurrentMonth
      write :
      A_CurrentMonth

      • spandy34's avatar
        spandy34
        Responsive Resident
        4 years ago
        Here is the error message
         

         

         
        Amount Recovered =
        VAR A_CurrentMonth = [Current Month Succ Rec uninsured loss]--insert your measure for Current Month - A. Uninsured Loss Recoveries
        VAR A_YearTD = [YTD Recoveries No Successful Recoveries Uninsured Loss]--insert your measure for YTD - A. Uninsured Loss Recoveries
        VAR A_Total = [Total Recoveries No Successful Recoveries Uninsured Loss]--insert your measure for Total - A. Uninsured Loss Recoveries
        VAR B_CurrentMonth = [Current Month Succ Rec Motor Recoveries]--insert your measure for Current Month - B. Motor Recoveries
        VAR B_YearTD = [YTD Recoveries No Successful Recoveries MV]--insert your measure for YTD - B. Motor Recoveries
        VAR B_Total = [Total Recoveries No Successful Recoveries MV]--insert your measure for Total - B. Motor Recoveries
        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
        )