- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Tableau User - LoD Functions in PowerBI dont work ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

yeahnah

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2018
10:58 PM

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

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

)

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

Thanks,

Patt

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018
05:24 AM

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:

- 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) - Remove all other filters
- 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

Proud to be a Datanaut!

10 REPLIES 10

Ross73312

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2018
11:12 PM

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.

Proud to be a Datanaut!

yeahnah

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
03:20 PM

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

Ross73312

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
06:50 PM

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?

Proud to be a Datanaut!

yeahnah

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
07:22 PM

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%

v-danhe-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018
01:44 AM

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:

Formula:

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

Result in table:

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018
05:24 AM

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:

- 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) - Remove all other filters
- 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

Proud to be a Datanaut!

yeahnah

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018
09:30 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018
10:03 PM

yeahnah

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018
10:43 PM

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