Matrix Measure - Show sum of detailed rows on each detailed row

[Anonymous]

Hello,

I am creating a matrix that shows a list of vessels, and has detail rows showing the various ports that each vessel will be visiting. Each port has an associated region (Americas, Asia, Middle East, Europe/ROW). I have a "Region" slicer that does not have a relationship with the vessel data, but is being used by a measure, "Measure 1", to determine if a given Vessel/Port combination belongs to one of the selected regions. The measure assigns a value of 1 if the port belongs to a selected region, and a value of 0 if it does not.

This is all working fine, but the next step is where my issue lies. If ANY of the ports associated with a vessel belong to one of the selected regions, I want ALL of the ports for that vessel to be displayed in the matrix. My thought was to create a second measure, "Measure 2", that will sum "Measure 1" for each vessel, such that "Measure 2" could be displayed in the matrix alongside each port and still display the "Measure 1" total for the entire vessel. I would then use "Measure 2" as a visual filter in the matrix so that records where "Measure 2" > 0 would be displayed.

Here is an Excel sample showing my desired result:

Selected Ports = Americas, Asia
Vessel	Port	Measure 1	Port Region	Measure 2 - Current	Measure 2 - Desired
Vessel 1	Texas	1	Americas	1	5
Vessel 1	Ecuador	1	Americas	1	5
Vessel 1	Peru	1	Americas	1	5
Vessel 1	Peru	1	Americas	1	5
Vessel 1	Chile	1	Americas	1	5
Vessel 2	South Korea	1	Asia	1	7
Vessel 2	South Korea	1	Asia	1	7
Vessel 2	South Korea	1	Asia	1	7
Vessel 2	China	1	Asia	1	7
Vessel 2	Singapore	1	Asia	1	7
Vessel 2	Angola	0	Europe/ROW	0	7
Vessel 2	Nigeria	0	Europe/ROW	0	7
Vessel 3	Greece	0	Europe/ROW	0	4
Vessel 3	Italy	0	Europe/ROW	0	4
Vessel 3	Singaport	1	Asia	1	4
Vessel 3	China	1	Asia	1	4

In the sample above, "Measure 1" correctly identifies when a port belongs to one of the selected regions (Americas, Asia). However, "Measure 2" is not currently working as desired. It seems to simply be duplicating the logic of "Measure 1", however I would like it to represent the sum of "Measure 1" for the vessel as a whole (see "Measure 2 - Desired" column). Here is my DAX for both measures:

Measure 1 =

var SelectedRegions = CONCATENATEX(ALLSELECTED(Regions[Region]), Regions[Region], ",")

var str_SelectedRegions = SUBSTITUTE(SelectedRegions, ",", "|")

var str_len = PATHLENGTH(str_SelectedRegions)

var tbl_list = ADDCOLUMNS(

GENERATESERIES(1, str_len),

"table",

PATHITEM(str_SelectedRegions, [Value])

)

var lst = SELECTCOLUMNS(tbl_list, "list", [table])

return

SUMX(

'Voyage Schedule',

if(

'Voyage Schedule'[col_Region_port] in lst,

1,

0

)

)

Measure 2 =

sumx(

values('Voyage Schedule'[VoyageNumber]),

[Measure 1]

)

[Iamnvt · Accepted solution]

hi,

I think there is something wrong with your desire result. Eg. if you want to sum of Measure 1 for each Vessel 2, the result should be 5, not 7.

if my understanding is correct, then you can use this measure:

new measure 2 = SUMX(ALLEXCEPT(Table1, Table1[Vessel]), [sum of measure 1])

the ALLEXCEPT will ignore all the outer filter from table: port, port regions, except Vessel column, it will sum up the result of Measure 1 for Vessel column only.

here is the PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk6pV_kVCZORV97Zz7g

[Anonymous]

Wow, that's exactly what I am looking for. Thank you so much!