Forum Discussion

m_wex's avatar
m_wex
Icon for Helper I rankHelper I
5 years ago

Hitting a Roadblock

Good evening. I am relatively new to PowerBi and have hit a roadblock and hoping for some help from the community. There is a table for the invoiced sales which is linked to a table containing the co...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

m_wex , Power bi context sensitive. So If you use any thing other than category , then it will give top 5 inside that.

 

You can top 5/N category

Measure =

var _cat = TOPN(10,all('Product Category (2)'[Product Category] ),[Total Sales],DESC)

return

calculate([Total Sales], filter( 'Product Category (2)','Product Category (2)'[Product Category] in _cat))

 

This should give you only top category

 

 

refer this how sub category works - https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

 

  • m_wex's avatar
    m_wex
    Icon for Helper I rankHelper I
    5 years ago

    amitchandak: Thank you for the quick response. Will give this a go. 

    • m_wex's avatar
      m_wex
      Icon for Helper I rankHelper I
      5 years ago

      Was not able to get the desired result with this. The main result (product category) is shown using a check box for top sales (5, 10, 25, all). If 5 is selected then only the top 5 categories sales are shown on a bar chart. What I am tring to do next is display another graph showing sales by sub-groups only for the category groups from the first graph. In my real world case for 5 catgories there would be 11 sub-groups. The results from one graph are used as a filter for a second graph.