Forum Discussion

PeterChen's avatar
PeterChen
Icon for Helper I rankHelper I
2 years ago
Solved

How to fetch number from visual matrix

I have a data like this:

## TABLE1

GroupTypeNum_Col_1 Num_Col_2
AX1050
BX201000
CX30900

 

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]

GroupMeasure1Measure2Measure3
A53100%
B50250%
C3010%

 

 

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?

General Comment
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    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.

     

4 Replies