Is it possible to create de-duplicated hierarchies for drill downs in Power BI?

[Anonymous]

Hi there,

Here is my dilemma: I have a table of product sales data that looks like this:

I want to be able to aggregate this data by 'Category'. However, as it is possible that the Apple, Orange and Pear were all purchased within the same order number, the aggregated data (de-duplicated) could look something like this:

I have imported the raw data tables to show both levels of aggregation shown above.  How can I create a hierarchy using the data from a combination of these two tables in order to create a dynamic 'Number of Orders' (which is dependent on the level in the hierarchy being displayed)?

I've searched a number of forums and experimented for a few hours on own but cannot find a solution.

Thanks for your help!

[dax · Accepted solution]

Hi @Anonymous , 

Did you mean that you want to get below result?

 If so, you could try below measure to see whether it work or not

Measure 3 = if(HASONEVALUE('Table'[category]),MIN('Table'[amount]),SUMX(SUMMARIZE('Table','Table'[category], "a", MIN('Table'[amount])),[a]))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax,

Yes, this is EXACTLY what I've been looking for.  Thank you so much. 

Please can you talk me through how this calculated measure is working (I'm quite new to Power BI)? 😀

[dax]

Hi @Anonymous , 

Because for the expression and context the grand total is not work, so we need to redefine the expression for grand total. we need use summarize function to redefine the table and simplify the table stracture which group by category and have a column to show the min value for each category, then you could sum the min column as grand total.  And you also could refer to /hasonevalue-vs-isfiltered-vs-hasonefilter/  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.