locked
Dynamically generate the categories (dimension members) in a chart using DAX RRS feed

  • Question

  • We want to build a report to show the top 10 or top 5 customers plus the others as a single category. The report also needs to be parameterized by product category, business type, region, etc. The chart type could be a stacked bar chart or also pie charts.

    Example:

    example chart

    The challenge is to generate the categories or subcategories dynamically using DAX.

    The values cannot be persisted in the dataset, because the needs to react dynamically to either, the ranking number (top 5 or top 10) and the contexts (by product, by business type, region, etc…)

    The equivalent in MDX would be a new dimension member.

    Have you faced a similar challenge?

    Any comment will be appreciated.

    Kind Regards,

    Paul

    Tuesday, October 11, 2016 10:03 AM

Answers

  • Hi everyone,

    many thanks for your contribution.

    We found a solution with a mix of your ideas:

    First we created a parameter for the "N" of the top N ranking (to let the user select which top wants to see):

    TopXParameter

    Also a "CustomerPlusAll" was required, with 0 transactions for the All other member:

    Now the formula for the total sales:

    Top Customer Sales = IF([TopRank] <= [TopXParameter],'Customers'[Total Sales], IF([IsOTHER], [Other Sales]))

    which basically proofs if the TopRank is smaller as the parameterized TopN:

    TopRank = RANKX( ALLSELECTED(Customers),'Customers'[Total Sales],,DESC,Dense)

    If it belongs to the Top N then the "regular measure" will be used, if not I let only the "ALL OTHERS" survive:

    IsOTHER = IF( HASONEVALUE(Customers[Customer Name]), IF(VALUES(Customers[Customer Name]) = "ALL OTHERS",true,false))

    And finally this member receives the sum of the other customer which do not belong to the top N.

    Other Sales = CALCULATE([Total Sales], FILTER(All(Customers), [TopRank] > [TopXParameter]))

    The result, as desired:

    Monday, October 17, 2016 9:53 AM

All replies

  • Would the following link answer your question?

    http://www.powerpivotpro.com/2012/07/dynamic-topn-reports-using-powerpivot-v2/

    Regards

    Laurence


    Tuesday, October 11, 2016 2:21 PM
  • Hi Laurence,

    thanks for the answer.

    That's almost what I need, the big challenge for me is to select for example the top 10 and shown their values plus another row with the summarized value for the others, which is just a bucket with the rest (please note that I faked the picture with paint):

    Tuesday, October 11, 2016 2:59 PM
  • then see http://www.powerpivotpro.com/2014/11/displaying-top-n-bottom-n-and-all-others/

    -sff

    Tuesday, October 11, 2016 3:53 PM
  • Hi sff, thanks for your help.

    This is also almost what I need, I still need to represent the data using stacked bar charts in Power BI and I need the calculations in the same hierarchy.

    What I get:

    What I need:


    Wednesday, October 12, 2016 6:51 AM
  • Hi Paul playing with BI ,

    In Power BI Desktop, it’s not possible to create a chart which can combine different level(The Customers and Others) in Stacked column chart together. Because it can’t determine which group of series need to be “stacked”. And it’s still not supported to add multiple fields into Legend of a chart.

    For your requirement, I suggest you add a column to assign type “Top5” and “All Others”, then build a hierarchy like “Top 5->Customer”. Put the hierarchy on X-axis, you suppose to drill down the detail level to see those different customers.

    Best Regards,
    Angelia


    Wednesday, October 12, 2016 7:40 AM
  • Hi Paul,

    For a set of labels to be used in a chart (on axis or legend) the set needs to be in the model as values in a column. My suggestion would be to create a table with all customers, plus a row for 'all others'. The trick now is to relate the top 10 customers to themselves in the new table, and all others to the 'all others' row. You can do this either through a relationship on a calculated column, e.g.

    = IF(RANKX(ALL(Customer), [TotalSalesQty]) <= 10, Customer[ID], "All others")

    or without a relationship, but done in a measure; which is like the solution that sff linked to.

    The idea in both cases is that you put customer names from the new table in the row labels or legend, and that a result is returned only for the top 10 customers and for 'All others'. The calculated column solution is somewhat less dynamic, but would save some calculation time when rendering the output.

    Wednesday, October 12, 2016 7:48 AM
    Answerer
  • Hi Michiel,

    thanks for your help!

    The calculated column solution would solve the problem if I had only a top 5 requirement.

    The challenge here is the user want to select between top 5, 10, 15 or 20.

    Since the calculated columns are not dynamic, or better, they are not evaluated after the model is processed, I cannot change the N value in the ranking function.

    On the other side, in the calculations I cannot change the name of an attributes, I mean, I cannot overwrite the name of the customers with "Other" dynamically for the ones that not belong to the top N.

    Wednesday, October 12, 2016 9:57 AM
  • taking out the "Top 5" row label heading then, would be the quickest way to achieve the "flattening" I think you're after.

    i think it's possible to programmatically generate essentially a UNION'd table and shove the All Others into it along with the "real" buckets, but it looks harder/more computationally expensive than it's worth, to me.

    For tools to go that route, I have in mind things like the following:

    http://sqljason.com/2013/02/union-operation-in-dax-queries.html

    http://www.daxpatterns.com/dynamic-segmentation/

    http://www.powerpivotpro.com/2014/11/new-twist-for-dynamic-segmentation-variable-grain-range-selection/

    I've never done this, so can't help with details.

    rood ruck!

    -sff

    Thursday, October 13, 2016 2:38 PM
  • My line of thinking is this.

    Let's call the table with customers plus the all others row, CustomersPlusAll. And let's assume we have a measure that returns the number of customers to be shown (5, 10, 20, etc.), named [TopRequired].

    Create a measure with (something like) the formula below:

    VAR top = [TopRequired]

    RETURN

    SUMX(CustomersPlusAll,

        IF(CustomerPlusAll[Customer] = "All others",

            CALCULATE([TotalSalesQty], FILTER(ALL(Customer), RANKX(ALL(Customer), [TotalSalesQty]) > top)),

            CALCULATE([TotalSalesQty],

                FILTER(ALL(Customer),

                      Customer[ID] = CustomerPlusAll[ID]

                      && RANKX(ALL(Customer), [TotalSalesQty]) <= top)

                )

          )

    )

    What this does, is to loop over the CustomerPlusAll table. For each customer, the sales quantity will be returned if and only if the customer is within the required top range. For the 'all others' customer, the total sales quantity will be returned for all customer that are not within the required top range.

    Note that there should be no relationship between the Customer and the CustomerPlusAll tables, therefore we need the Customer[ID] = CustomerPlusAll[ID] clause to link the two. I didn't build a model to check this, so you may need to tweak some details.

    Friday, October 14, 2016 7:14 AM
    Answerer
  • Hi everyone,

    many thanks for your contribution.

    We found a solution with a mix of your ideas:

    First we created a parameter for the "N" of the top N ranking (to let the user select which top wants to see):

    TopXParameter

    Also a "CustomerPlusAll" was required, with 0 transactions for the All other member:

    Now the formula for the total sales:

    Top Customer Sales = IF([TopRank] <= [TopXParameter],'Customers'[Total Sales], IF([IsOTHER], [Other Sales]))

    which basically proofs if the TopRank is smaller as the parameterized TopN:

    TopRank = RANKX( ALLSELECTED(Customers),'Customers'[Total Sales],,DESC,Dense)

    If it belongs to the Top N then the "regular measure" will be used, if not I let only the "ALL OTHERS" survive:

    IsOTHER = IF( HASONEVALUE(Customers[Customer Name]), IF(VALUES(Customers[Customer Name]) = "ALL OTHERS",true,false))

    And finally this member receives the sum of the other customer which do not belong to the top N.

    Other Sales = CALCULATE([Total Sales], FILTER(All(Customers), [TopRank] > [TopXParameter]))

    The result, as desired:

    Monday, October 17, 2016 9:53 AM
  • very cool. i'm still thinking about a solution that doesn't involve adding any rows to the static data, since i can see myself wanting to do this in a case where that isn't an option.

    thanks for sharing your solution,

    sff

    Monday, October 17, 2016 2:48 PM