Dynamic Grouping in Power BI/PowerPivot Model based on a calculated field

Question

We are trying to create a model in Excel/Power BI (using Power Query or Power Pivot or anything that would work) in order to classify a customer by its best product (based on a ranking system).

The first approach we applied was to count the customers per minimum ranking (or per best product brand). (inspired from the blog https://stackoverflow.com/questions/15742186/powerpivot-dax-dynamic-ranking-per-group-min-per-group)

Below the steps we did exactly: - In PowerPivot Model, we created Classification and Customers table like in the example further below.

• In the same model, we added a calculated column with the following formula to obtain the minimum rank per customer.

  =MINX (
  FILTER ( ALLSELECTED ( Customers ); [Customer_ID] = EARLIEST ( [Customer_ID] ) );
  [Ranking]
  )
  

• Within a pivot table in Excel, we’ve put the calculated column in rows.

• Then, we’ve used a Count distinct aggregation of the customers in the pivot table values.  This gave me the first desired result. (below example Pivot_Table.Selection1)

Now, the issue comes when we want to add more analysis axis. For example, besides the product brand, we want to have the Product type in columns, and we want our measure to be recalculated every time I add/delete an axis. In other words, we want to have a distinct customer count per best product and per Product Type. In addition, we want the second attribute (axis) to be variable and the grouping or the distinct count per group to be dynamic.

Example:

Let’s suppose we have the tables Classification and Customers in our Model:

In the first approach we tried, we got the following table: Pivot_Table.Selection1:

Now when we add the analysis axis, we would like to have the following example: Pivot_Table.Selection2:

But we are having this:

As you can see, there should be one customer for the Group “Mercedes” and one for “Renault”, since depending on the product type, the top Truck for customer A is Renault and its top Car is “Mercedes”. However, in the pivot table, the Mercedes group is shown as Truck (which doesn’t even exist in our dataset).

Edit

I'm open for any suggestion, not only Power Pivot, but also Power Query (M functions) or Power BI or whatever could work.

Answer 1

Finally I think I understood your problem, a customer can have different Product_Brand values, you want to count only those Product_Brand which its ranking is the minimum.

In that case, this is a possible solution:

Create a calculated column called Minimum Rank in the Customer table.

=
CALCULATE (
    MIN ( [Ranking] );
    FILTER ( Customer; [Customer_ID] = EARLIER ( Customer[Customer_ID] ) )
)

Then create a measure, lets say Customer ID Distinct Count to count those rows where the Rank is equal to the minimum for that customer.

Customer ID Distinct Count :=
CALCULATE (
    DISTINCTCOUNT ( Customer[Customer_ID] );
    FILTER ( Customer; [Ranking] = [Minimum Rank] )
)

You will get something like this: