How to fetch number from visual matrix

[PeterChen]

I have a data like this:

## TABLE1

Group	Type	Num_Col_1	Num_Col_2
A	X	10	50
B	X	20	1000
C	X	30	900

I create a TABLE2:

TABLE2 = DISTINCT(TABLE1[Group])

I created 3 measures in TABLE2:

Measure1 = CALCULATE( DIVIDE(SUM(TABLE1[Num_Col_2]) , SUM(TABLE1[Num_Col_1])) , TABLE1[Type] = "X")

Measure2 = RANKX( ALL(TABLE1[Group]), TABLE2[Measure1 ],,ASC)

Measure3 = DIVIDE([Rank_EV_LC] - 1, COUNTROWS(TABLE2) - 1)

Visual Matrix:

Rows: TABLE1[Group]

Values: TABLE2[Measure1], TABLE2[Measure2], TABLE2[Measure3]

Group	Measure1	Measure2	Measure3
A	5	3	100%
B	50	2	50%
C	30	1	0%

For now, everything is perfect.

However, what I want is to create a filter/slicer and I can fetch the corresponding Measure3 value.
For examle, if I do a filter with SELECTEDVALUE(TABLE1[Group]), and let's say selecting B, I want a visual card or whatever similar to show correct value "50%".

My currect slicer will change whole, when I select B, it will recalculate

If my slicer use TABLE2[Group] and I select B, Measure3 becomes blank, Measure1/2 look good but it still show all.

The second way is to use TABLE1[Group], it only shows B but the Measure2 and Measure3 is incorrect...

Any idea?

[v-zhangti · Accepted solution]

Hi, @PeterChen 

You can try the following methods. Measure 1 No change.

Measure2 = RANKX(ALL(TABLE2[Group]), TABLE2[Measure1],,DESC)

Measure3 = DIVIDE([Measure2] - 1, COUNTROWS(ALL(TABLE2)) - 1)

 TABLE2[Group] as a slicer.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I'd like to try.  Share the download link of the PBI file.

[PeterChen]

Please check this out... same issue
https://community.fabric.microsoft.com/t5/Desktop/RANKX-value-changes-for-filtered-value-when-it-sho...

[lbendlin]

Read about Filter Context.  Your card visual has no idea what SELECTEDVALUE means, so you need to help it expand the filter context back to the entire group when B is in context, for example via REMOVEFILTERS.