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://stackguides.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.

I am trying to say hello but stackoverflow isn't appreciating us saying hello :D. — Rami Ouanes
the issue is related to the measure you are using, are you using the default distinct count in pivot table or have you created a measure for that? — alejandro zuleta
Hello, I am actually using the default distinct count. Are you saying I need to create the measure in the powerpivot directly? — Rami Ouanes
Yes, default distinct count measure will take in account each customer id in the given context of the pivot. — alejandro zuleta
Hello Alejandro, I have tried creating a distinct count measure in the PowerPivot model but it's still not working, the issue is that now it's showing "Renault" even if I don't add the "Product Type" in columns... So it's still not refreshing the calculation but this time not even for the first axis which is the static axis in my need. — Rami Ouanes

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-13T19:16:33

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:

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

Edit

1 Answers