cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filtering DAX Calculations on Multiple Dimensions with Slicers - Tableau User

Hey everyone!

Hope everyone is having a nice day.

So I am currently struggling with getting my DAX Calculations to filter the data based on slicers.

In Tableau what I want to use is called a Context Filter which filters the data before the calculation occurs ensuring that I am only performing the calculation on the data required.

 

Is there is another way to go about this in PowerBI?

 

Below is a link to download:

https://spaces.hightail.com/receive/T4FmHnr1uF

Raw Data.xl

Power BI Workbook

Tableau Workbook

 

For those who can't download.

 

The Calculations I am currently using

PA_Description = Type of Promotion

  • This Column shows the different types of Promotions
    • 222, 444, 777.. etc are all Promotion Names
    • Null is everything that was not sold on a promotion

Total Promo Items Sold - formula: {fixed [PA Description] : sum([Qty])}

  • This Column sums the quantity all the products sold ON Promotion for each PA_Description

Total Products Quantity - formula: {fixed [Productcode1] : sum([Qty])}

  • This Column sums ALL the products sold ON Promotion & OFF Promotion for each PA_Description

Promo Redemption - formula: sum([Total Promo Items Sold])/sum([Total Products Quantity])

  • This Column returns the % of Products in a promotion that sold as a promotion

Calculations in Power BI

 

 

Total Products Quantity = CALCULATE(
    SUM('pbi'[Qty]),
    ALL ('pbi' ),
    VALUES ('pbi'[Productcode])
)

Total Promo Items Sold = CALCULATE(
    SUM('pbi'[Qty]),
    ALL ( 'pbi' ),
    VALUES ('pbi'[PA_Description] )
)

 

Example: USING ONE PRODUCT

Product 51 sold 10 units for Promotion 222

Product 51 sold 10 units for Null (not on promotion)

 

PA Description     Total Items Sold In Promo     Total Products Quantity      Promo Redemption

222                     10                                         20                                      50%

Null                     10                                         20                                      50%

 

 

 

Pictures below

Show both workbooks with NO FILTERS SELECTED -Results are the same! 😄

 Power BI with FiltersPower BI with FiltersTableau with FiltersTableau with Filters

 

 

Pictures below

Show both workbooks with FILTERS SELECTED - Results are different :'(

Power BI No FiltersPower BI No Filters

 

Tableau with FiltersTableau with Filters

If anything is unclear please reply and I'll do my best to explain myself better.

Thanks All!

3 REPLIES 3
Highlighted
Resolver II
Resolver II

Re: Filtering DAX Calculations on Multiple Dimensions with Slicers - Tableau User

What do your relationships look like?

Have you tried adding more ALL statements in the calculation for your other tables?

e.g.

Total Products Quantity = CALCULATE(
SUM('pbi'[Qty]),
ALL ('pbi' ),
ALL ('tbl1'),
VALUES ('pbi'[Productcode])
)

Highlighted
Frequent Visitor

Re: Filtering DAX Calculations on Multiple Dimensions with Slicers - Tableau User

Hi @jd009

I only have one table in this workbook - is it better to work with more tables in Power BI?

Highlighted
Resolver II
Resolver II

Re: Filtering DAX Calculations on Multiple Dimensions with Slicers - Tableau User

Hi @yeahnah,

 

I have to honest, this has confused me a lot! I've downloaded your pbix file and I have a couple of questions:

- why would there be any 'Total Promo Items Sold' in the "NOT ON PROMO" PA_Description?

- since you're using only 1 field to determine both field values, the Qty will always be the same for both measures - is there another field to base one of the measures off?

 

 

I've "fixed" one of your formulas so you get the 1506 value you want, just use:

=SUM('pbi'[Qty])

 

To fix the other fomula I'd need more information on how it should be correctly calculated.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors