Forum Discussion
How to make these KPIs? found them really cool through reddit.
- 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
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