Forum Discussion

qthuynh's avatar
qthuynh
Frequent Visitor
8 years ago

Subtotal based on Dynamic Measure

Hi,

 

I created a dashboard with built-in dynamic switch to show the top and bottom 5, 10, and 15 performers by products and salespeople (similar to this dashboard). We have over 500+ products and 200+ salespeople.

 

Based on the dashboard link, Total revenue was $4B. The dashboard displays the top 5 products by revenue when selected, but mgt wants to see the total top 5 and % of the total (e.g. total products revenue were $4B. Top 5 product sales is $0.58B. Top 5 % of Total iss 14.5%).

 

Is there a measure that can summarize only the top/bottom products that are being displayed?

 

 

Link to download working dashboard with dynamic filters.

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi qthuynh,

     

    It could be a measure as below. Can you share a sample please?

    Measure =
SUMX (
    TOPN (
        5,
        SUMMARIZE (
            FactSales,
            'DimProduct'[ColorName],
            "salestotal", SUM ( FactSales[SalesQuantity] )
        ),
        [salestotal]
    ),
    [salestotal]
)

    Best Regards,

    Dale

    • qthuynh's avatar
      qthuynh
      Frequent Visitor
      8 years ago

      v-jiascu-msft

       

      Sample data has been added to the post above. I need a way to subtotal the (Top/Bottom 5, 10, 15) savings or spend that is being displayed in the bar chart.