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

Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Hey guys.

I am trying to do a LoD Calculation in Power BI - but it doesn't work like it does in Tableau and I cant figure out what I am doing wrong.

 

Calculations I am using in Tableau Listed Below

 

Total Promo Items Sold - "This Calculations sums all items sold on a specific promotion PA Description"

{fixed [PA Description] : sum([Qty])}

 

Total Products Quantity - "This Calculation sums all the items sold that could of been in the promotion"

{fixed [Productcode1] : sum([Qty])}

 

Promo Redemption - "This Calculation shows the Percentage of Products that are sold on Promotion"

sum([Total Promo Items Sold])/sum([Total Products Quantity])

tt.PNG

 

So I am trying to gather all Productcodes in PA Description and sum the quantity of those items

 

Power BI Formulas I have.

 

Total Promo Items Sold: - WORKS!

Total Units = CALCULATE(
    SUM('data'[Qty]),
    ALLEXCEPT('data', 'data'[PA_Description])
)

 

Total Products Quantity - Does not give me the correct answer :'(

Total Products Quantity = CALCULATE(
    SUM('data'[Qty]),
    ALLEXCEPT('data', 'data'[Productcode])
)

pbi.PNG
If someone has a workaround for this that'd be great!


Thanks,

Patt

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

@yeahnah

 

The Power BI equivalent of a Tableau FIXED LOD expression is a CALCULATE ( ...., ALL(...), VALUES(...) ) pattern.

 

This is because, in Tableau, the expression {fixed [PA Description] : sum([Qty])} translates to:

  1. Create a filter equivalent to the currently visible values of [PA Description] (whether due to direct filters on [PA Description] or cross-filtering from other columns)
  2. Remove all other filters
  3. Calculate SUM([Qty]) in this new context

To get the same result in Power BI using the data from POWER BI DATA.xlsx, I would rewrite your two measures as:

 

Total Units =
CALCULATE(
    SUM('data'[Qty]), 
    ALL ( 'data' ),
    VALUES ('data'[PA_Description] )
)

Total Products Quantity =
CALCULATE(

    SUM('data'[Qty]),
    ALL ('data' ),
VALUES ( 'data'[Productcode] )

)

 

In situations where the column you want to retain as a filter is in a related table, you may need to use SUMMARIZE rather than VALUES.

 

Here is a good article related to what you are doing here:

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




10 REPLIES 10
Super User
Super User

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

What is the relationship of Product Code with your data?  Based on the context of how your running this measure within the visual i'm not surprised you are getting this particular result.

 

The statement you have written is essentially saying "Ignore all filtering context, except for the filtering context applied to the Product Code".  In the table you have shown, there is no product code item.  This indicates to me that there is no Product Code context to maintain.

 

 

There will be a solution, we just need to understand your data better.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


yeahnah Frequent Visitor
Frequent Visitor

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Hi Ross,

 

Product Code is a unique product identifier

PA_Description defines a promotion

  • A Promotion can contain multiple Product Codes
  • Different Promotions can contain the same Product Code

I'm not sure how to upload a workbook or dataset on here so I've uploaded them to Hightail so anyone can download

 

https://spaces.hightail.com/space/kajDUnf52K

  • Excel Data Set
  • Power BI Workbook
  • Tableau Workbook

 

Super User
Super User

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Sadly my companies polices mean i'm prevented from downloading your files.

 

What we need to understand is what is the logic here for the count?  Based on your formulas, you are reusing the same QTY column for your count.  So given a particular PA_Description, what logic are you wanting to apply to get the other count?  Is it a case of getting the distinct values of the Product Codes, then finding out what the total quantity of those product codes are added together?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


yeahnah Frequent Visitor
Frequent Visitor

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

I am working with a Transactional Database - with each line in each transaction recorded

 

https://imgur.com/a/mjHA3mc - picture of data set & product hierarchy

 

If you refer to the Tableau Picture I can explain what is happening in each column

 

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.

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%

 

Community Support Team
Community Support Team

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Hi @yeahnah,

Based on my test, in Power BI, the data could be aggregated in the table, you could just use the CALCULATE(SUM(YourTable[yourcoulumn])) to get the correct result.

Sample:

1.PNG

Formula:

Measure = CALCULATE(SUM(Table1[A]))
Measure2 = CALCULATE(SUM(Table1[B]))

Result in table:

1.PNG

If I misunderstand you, please just let me know.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

@yeahnah

 

The Power BI equivalent of a Tableau FIXED LOD expression is a CALCULATE ( ...., ALL(...), VALUES(...) ) pattern.

 

This is because, in Tableau, the expression {fixed [PA Description] : sum([Qty])} translates to:

  1. Create a filter equivalent to the currently visible values of [PA Description] (whether due to direct filters on [PA Description] or cross-filtering from other columns)
  2. Remove all other filters
  3. Calculate SUM([Qty]) in this new context

To get the same result in Power BI using the data from POWER BI DATA.xlsx, I would rewrite your two measures as:

 

Total Units =
CALCULATE(
    SUM('data'[Qty]), 
    ALL ( 'data' ),
    VALUES ('data'[PA_Description] )
)

Total Products Quantity =
CALCULATE(

    SUM('data'[Qty]),
    ALL ('data' ),
VALUES ( 'data'[Productcode] )

)

 

In situations where the column you want to retain as a filter is in a related table, you may need to use SUMMARIZE rather than VALUES.

 

Here is a good article related to what you are doing here:

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




yeahnah Frequent Visitor
Frequent Visitor

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Hey @v-danhe-msftthanks for getting back to me!

I could aggregate it but I need some of the other values at a lower level than PA_Description so this won't work for me :'(

yeahnah Frequent Visitor
Frequent Visitor

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Hey @OwenAuger

 

Thanks!

This is exactly what I was after.

yeahnah Frequent Visitor
Frequent Visitor

Re: Tableau User - LoD Functions in PowerBI dont work the same? plz halp

Hey @OwenAuger

 

Sorry to bother you.

I was wondering if you know how to apply slicer filters to the DAX code?


In Tableau they are called 'context filters' - This filters the data before the calculations occurs. This makes them Dynamic which is awesome!!

I have found a way to do that involves me creating ALOT of tables and referencing them but this would take me days to complete.

If you know of anything that could help that would be amazing.

Thanks,

Patrick