Visualisations Advice

[spandy34]

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 

[tamerj1]

Hi @spandy34 

what are your measures? Is Everything in one table?

[spandy34]

All measure are from the same table named Main Claim Data

Relationships

 

Uninsured Loss Recoveries Column Measures

1.  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 */))

1.  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)

1.  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]

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]

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]

@spandy34 
First define all measures seperately the do like this:

VAR B.YearTD = [YTD Recoveries No Successful Recoveries MV]

[spandy34]

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]

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

[spandy34]

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

)

[tamerj1]

You have to change those as well

 

[spandy34]

Thats worked - there are no errors.  So where do you want me to put the fields in the cross tab visualisation like this or is there a stage inbetween?

[tamerj1]

Hi @spandy34 
It should work but we need to trick PowerBi in oreder to reach our distination. See in this screenshot is working with me 

In order to trick it we need to modify the code for 'Table'

Table = { ( "Current Month", "A. Uninsured Loss Recoveries", 1 ), ( "YTD", "B. Motor Recoveries", 2 ), ( "Total", "Total", 3)}

Now follow the following steps: please follow the mouse curser

[spandy34]

Hi @tamerj1 

The help you have provided has been amazing - here is the result below.  There are the other rows to add ie Amount Recovered, Team Fee Income for both Uninsured Loss Recoveries and Motor Recoveries.  Is this too much to try and help me with or are you okay too?  I understand I have take a lot of your time already?

[tamerj1]

USe the same measure. the only thing yuou need to change is the measure names inside the master measure. So you will have total 3 master measures. Create the other two and add them to the values of the matrix

[spandy34]

Im my example can you tell me what meaure you are referring to when you say " Use the same measure" do you mean copy them ?

[tamerj1]

@spandy34 

Any luck?

[tamerj1 · Accepted solution]

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

Hi @tamerj1 I have not been able to copy the other tables and put the measure is as I am trying to get my head around what you mean but I feel this is resolved once I make the other two measure.  Many thanks for your help.

[tamerj1]

@spandy34 

Please let me know if you need any help on that or any other subject. 

[tamerj1]

Hi @spandy34 

I sent you a private measure. Please check and reply

[spandy34]

Here is the table - looking more like we need - the values look very high