Power BI awkward to use when not working with rows that have not already had calculations applied

[Anonymous]

Hi,

I frequently face issues using Power BI due to filters / columns applying to rows, which should only be looked at when summed up. For example, most datasets I use will have rows as invoices that you present on an aggregated level to show something like sales by product. However, using Power BI, all of the easy-to-use functions are all set up for row-level calculations, which are often irrelevant in these cases.

A few examples of things I want to do:

• filter out products with sales under £1000 - cannot use the filter pane and say "sales values less than £1000" as this only filters out invoices of less than 1000
• categorise products by sales value - would like to group the products into size categories without using a separate mapping e.g. "sales of less than £1000", "sales of £1000 - £10,000", "sales over £10,000". but using the "group data" or binning functions again only looks at the invoices, not the total product sales

I'm aware that some of these can be achieved using complex DAX, but for beginners this isn't a good solution.

Is there a simple solution I am missing?

If not, how can I go about categorising products into sales value groups?

[v-henryk-mstf]

Hi @Anonymous ,

Can you provide me with some test data and screenshots of the expected results for further testing? Looking forward to your reply.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Henry

[Anonymous]

Hi Henry,

Sure - I've made a fake example dataset as can't share actual numbers.

So for example - I want to filter the tables below to look at only products that have total sales of over $50,000. I can do this to the table on the right using the normal filter panel, but if I try to filter the page / the left table using the simple filters, it only removes invoices / rows that are less than $50,000.

I know there are workarounds with DAX, but I am teaching beginners so ideally don't want to complicate with that. Also many of the datasets i'm working with use direct query where there are limitations writing DAX.