Top 1 KPI Card for Selected Group

[milindwasekar]

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 .

[v-heq-msft · Accepted solution]

Hi @milindwasekar ,
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 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 post helps, then please consider Accept it as the solution to help the other members find it more quickly