Top 1 KPI Card for Selected Group

Question

factinternetsales is my Fact table and DimProduct is dimension table which is connected with fact table by product key . I created slicer for various dimesions like Productline ,Model name ,size,color,class,calendar year as field parameter from table DimProduct .

I want top one Sales as kpi card so that when I select any group like Productline ,Mode name ,size,color,class,calendar year KPI card should show Top1 from each Selected field from dropdown .

anonymous · Accepted Answer

Hi&nbsp;milindwasekar&nbsp;,Based on your description, I created some test data
DimProduct

ProductID
ProductLine
Color

1001
A
Blue

1002
A
Blue

1003
A
Red

1004
A
Red

1005
B
Red

1006
B
Red

1007
B
Green

1008
C
Green

1009
C
Green

1010
D
Green

1011
D
Orange

1012
D
Orange

1013
E
Orange

1014
E
Orange

FactInternetSales

ProductID
SalesAmount

1001
12

1002
24

1003
15

1004
42

1005
13

1006
47

1007
46

1008
52

1009
45

1010
78

1011
32

1012
11

1013
46

1014
31

Create calculate column in&nbsp;DimProduct
Color_Rank = 
VAR _rank = 
RANKX(
    ALL(DimProduct[Color]),
    CALCULATE(
        SUM(FactInternetSales[SalesAmount]),
        ALLEXCEPT(DimProduct, DimProduct[Color])
    ),
    ,
    ASC
)
RETURN
CONCATENATE(_rank,[Color])ProductLine_Rank = 
VAR _rank = 
RANKX(
    ALL(DimProduct[ProductLine]),
    CALCULATE(
        SUM(FactInternetSales[SalesAmount]),
        ALLEXCEPT(DimProduct, DimProduct[ProductLine])
    ),
    ,
    ASC
)
RETURN
CONCATENATE(_rank,[ProductLine])
Create filed parameter based on calculated columns
Parameter = {
    ("Color_Rank", NAMEOF('DimProduct'[Color_Rank]), 0),
    ("ProductLine_Rank", NAMEOF('DimProduct'[ProductLine_Rank]), 1)
}
Create KPI visualiztion

Final output

Best regards,Albert He
If this&nbsp;post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly
&nbsp;
&nbsp;

Forum Discussion

Top 1 KPI Card for Selected Group

1 Reply

Related content

Dynamic Cards by Selection on a Chart

Fabric & Azure Data User Group Program Requirements & Benefits

MULTIPLE CARD

Community User Group Experience

Card values depends on slicer selection

ProductID	ProductLine	Color
1001	A	Blue
1002	A	Blue
1003	A	Red
1004	A	Red
1005	B	Red
1006	B	Red
1007	B	Green
1008	C	Green
1009	C	Green
1010	D	Green
1011	D	Orange
1012	D	Orange
1013	E	Orange
1014	E	Orange

ProductID	SalesAmount
1001	12
1002	24
1003	15
1004	42
1005	13
1006	47
1007	46
1008	52
1009	45
1010	78
1011	32
1012	11
1013	46
1014	31