Forum Discussion

hyaa's avatar
hyaa
Regular Visitor
1 year ago
Solved

How to make these KPIs? found them really cool through reddit.

 
  • shafiz_p's avatar
    1 year ago

    Hi hyaa  You can acheive this using new KPI card visual and dax.

    To find out best selling product based on quantity, try below code:

     

     

     

    Best Selling Product = 
    VAR TopProduct =
        TOPN(
            1,
            SUMMARIZE(
                FactResellerSales,
                DimProduct[ModelName],
                "TotalQuantity", [OrderQty]
            ),
            [TotalQuantity], DESC
        )
    RETURN
        MAXX(TopProduct, DimProduct[ModelName])

     

     

     

    Where [OrderQty] = SUM(FactResellerSales[OrderQuantity])
    Similarly to find out Revenue, Sold Unit and Profit for this best product, you can try below code:

     

     

     

    Revenue_BestProduct = 
    VAR TopProduct =
        TOPN(
            1,
            SUMMARIZE(
                FactResellerSales,
                DimProduct[ModelName],
                "TotalQuantity", [OrderQty]
            ),
            [TotalQuantity], DESC
        )
    RETURN
        CALCULATE(
            [Revenue],
            DimProduct[ModelName] = MAXX(TopProduct, DimProduct[ModelName])
        )
    Sold_BestProduct = 
    VAR TopProduct =
        TOPN(
            1,
            SUMMARIZE(
                FactResellerSales,
                DimProduct[ModelName],
                "TotalQuantity", [OrderQty]
            ),
            [TotalQuantity], DESC
        )
    RETURN
        CALCULATE(
            [OrderQty],
            DimProduct[ModelName] = MAXX(TopProduct, DimProduct[ModelName])
        )
    Cost_BestProduct = 
    VAR TopProduct =
        TOPN(
            1,
            SUMMARIZE(
                FactResellerSales,
                DimProduct[ModelName],
                "TotalQuantity", [OrderQty]
            ),
            [TotalQuantity], DESC
        )
    RETURN
        CALCULATE(
            [Total Cost],
            DimProduct[ModelName] = MAXX(TopProduct, DimProduct[ModelName])
        )
    Profit_Best Product = DIVIDE([Revenue_BestProduct]-[Cost_BestProduct], [Revenue_BestProduct], 0)

     

     

     

     

    Now create 3 text measure based on the above 3 measure. Try below Code:

     

     

    RevenueFormat = "Total Revenue : $" & FORMAT([Revenue_BestProduct] / 1000000, "0.00") & "M"
    
    QuantityFormat = "Sold Quantity : " & FORMAT([Sold_BestProduct], "#,##0")
    
    ProfitFormat = "Profit Margin : " & FORMAT([Profit_Best Product], "0.00%")

     

     

     

     

    Now Select new card visual from visual pange.

     

    Place best selling product measure in the value section:

     

    Open format option, go to Reference label , place all 3 newly created text measure, and turn off title. See image:

     

    Also set other formating according to your need. Like turn on accent bar, set text color, set divider color etc. 

    Output:

    Other version, This time use title option in reference and used orginal measure.

    Which more similar to your provided image.

     

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

     

    Best Regards,
    Shahariar Hafiz