Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@Anonymous
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:
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
@Anonymous
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:
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
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
Hi again @Anonymous
Could you describe exactly how you want to use the "context filter" in this case and we might be able to come up with a solution.
There isn't a direct equivalent of Tableau's context filters in Power BI. All filters (report, page, visual, slicer or otherwise) are intersected simultaneously when DAX expressions are evaluated.
Off the top of my head, one idea is that we could produce the same effect as a context filter by using a disconnected table to provide the "context filter" and wrap a CALCULATE (..., TREATAS(...) ) around the rest of the DAX expression to create a filter that takes precedence over other filters.
Regards,
Owen
Hi @Anonymous,
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
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 :'(
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.
Hi Ross,
Product Code is a unique product identifier
PA_Description defines a promotion
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
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?
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
Total Promo Items Sold - formula: {fixed [PA Description] : sum([Qty])}
Total Products Quantity - formula: {fixed [Productcode1] : sum([Qty])}
Promo Redemption - formula: sum([Total Promo Items Sold])/sum([Total Products Quantity])
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%
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |