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
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!

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]
- spandy34
  Responsive Resident
  4 years ago
  Where do I define them?
  - 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
  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
    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

Forum Discussion

Visualisations Advice

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?