topic Re: Visualisations Advice in DAX Commands and Tips

Visualisations Advice

spandy34 — Wed, 23 Feb 2022 15:39:21 GMT

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

Re: Visualisations Advice

tamerj1 — Wed, 23 Feb 2022 16:00:17 GMT

Hi spandy34

what are your measures? Is Everything in one table?

Re: Visualisations Advice

spandy34 — Wed, 23 Feb 2022 16:30:47 GMT

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

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 07:41:15 GMT

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!

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 07:51:01 GMT

Of course the grand total is another subject. Let's first check if this works.

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 08:52:43 GMT

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

)

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 08:56:27 GMT

spandy34
First define all measures seperately the do like this:

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

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 09:28:29 GMT

Where do I define them?

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 09:33:45 GMT

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]

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 09:35:25 GMT

Copy the name as opposed to the full measure code?

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 09:36:39 GMT

Yes the name only but inside square brackets [ ]

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 09:49:04 GMT

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

)

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 10:04:09 GMT

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

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 10:52:06 GMT

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

)

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 10:56:46 GMT

You have to change those as well

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 11:04:03 GMT

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?

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 12:20:00 GMT

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

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 13:15:14 GMT

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

Re: Visualisations Advice

spandy34 — Thu, 24 Feb 2022 13:49:11 GMT

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?

Re: Visualisations Advice

tamerj1 — Thu, 24 Feb 2022 13:52:37 GMT

Great!
Now the measures are showing the grand total as they are not filtered by anything. So start selecting something on the the slicers and see the results.