About Calculating Gross sales relying on one other Worth in DAX


Thank you for reading this post, don't forget to subscribe!

Our typical Measures mixture the Values from columns in a single Truth desk. However what is required to calculate a consequence that will depend on Information in one other Truth desk? Let’s take a look at a attainable answer.

Salvatore Cagliari

Towards Data Science
Picture by Isaac Smith on Unsplash

What is required to calculate a consequence based mostly on a selected worth in DAX?

Easy: A filter.

However what if the filter needs to be a desk?

Thankfully, I already know find out how to work with tables in DAX Measures:

And now, my shopper gave me a problem to place my data to the take a look at:

Take a look at the next Report:

Determine 1 — On-line vs Retail Gross sales and offered merchandise (Determine by the Writer)

As you’ll be able to see, the variety of merchandise offered differs between On-line and Retail Gross sales.

My shopper requested the next questions:

  • What’s the On-line Gross sales Quantity for the Merchandise offered in Retail Shops as properly?
  • And which Merchandise are offered solely On-line?

Can I reply the primary query in Energy BI by making use of a Filter on the left desk like this?

Applying a Filter in Power BI filtering by the Retail Sales Measure (Figure by the Author)
Determine 2 — Making use of a Filter in Energy BI filtering by the Retail Gross sales Measure (Determine by the Writer)

Sadly, this doesn’t work.

The reason being that no Relationship connects these two tables, and the Filter course to Dimension tables works solely in a single course.

Sure, I might change it to bidirectional filtering. However this may introduce points because it won’t work in any state of affairs.



Leave a Reply

Your email address will not be published. Required fields are marked *