Filtering DAX Calculations on Multiple Dimensions with Slicers - Tableau User

[Anonymous]

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 FiltersTableau with Filters

Pictures below

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

Power BI No Filters

Tableau with Filters

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

Thanks All!

[Anonymous]

Hi @Anonymous,

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.

[Anonymous]

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])
)

[Anonymous]

Hi @Anonymous

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