Forum Discussion
Help Moving from Excel to PowerBI
Hi, I have a lot of Excel experience but very new to PowerBI. I have a dataset as follows:
| Fruit | Basket | Pounds | % Sweet | % Ripe |
| Blueberries | Basket A | 1 | 10% | 60% |
| Cherries | Basket A | 1 | 20% | 50% |
| Blackberries | Basket B | 2 | 30% | 40% |
| Strawberries | Basket B | 3 | 40% | 30% |
| Blueberries | Basket C | 4 | 50% | 20% |
| Cherries | Basket C | 5 | 60% | 10% |
| Shopper | Basket | Ownership |
| Shopper X | Basket A | 50% |
| Shopper X | Basket B | 50% |
| Shopper Y | Basket A | 25% |
| Shopper Y | Basket C | 25% |
| Shopper Z | Basket B | 25% |
| Shopper Z | Basket C | 75% |
In excel, I have an output that determines a weighted average % (by weight in pounds) of Shopper [#]'s basket for the % sweet and % ripe variables. Since I only need to do it for one shopper at a time, I figure out the "active" shopper's ownership of all of the fruits (index/match), and then do a sumproduct/sum for the weighted average. I'm getting tripped up on how to do this in PowerBI - since I need to do it for all shoppers at once, I'm lost on how to start going about it. Can anyone point me in the right direction? I'm still reading through all of the content/tutorials out there but couldn't make it work with the lookup function - it's for a somewhat time sensitive project so any help is very appreciated! Thank you!
4 Replies
Hi redred ,
Can you let me know what is the expected output you are looking at.
Also, Great that you are learning Power BI. It is an amazing BI platfrom.
Few tutorials which are helpful
https://www.analyticsvidhya.com/blog/2019/05/10-useful-data-analysis-expressions-dax-functions-power...
https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics
You Tube : https://youtu.be/m1eLTtZHGs4
DAX Fridays have a few Check there channel
Measures vs Calculated Columns: https://www.youtube.com/watch?v=nJSXty9Y4tM
DAX Fridays! #5: CALCULATE (Part 1) : https://www.youtube.com/watch?v=-oDpOfhgmzA
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleTo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Refer
https://community.powerbi.com/t5/Desktop/Dynamic-measure-calculation-for-hierarchy-data/td-p/592546
https://community.powerbi.com/t5/Desktop/How-to-create-column-and-row-hierarchies-in-tables/td-p/811...https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
DAX Fridays! #8: CALCULATE (Part 2): https://www.youtube.com/watch?v=uZ15A13AsHY
Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
The expected output would be that for Customer X for example, they own 50% of Basket A (i.e. 0.5lb blueberries / cherries) and 50% of Basket B (1lb blackberries / 1.5lb strawberries), so the weighted average sweetness & ripeness would be 30% & 40%, respectively (sumproduct owned weight of fruit x metric) / (sum of owned weight).
Anonymous
Hi redred ,
Please fine the link
https://drive.google.com/open?id=13kIvdoA3Lz33NL9pNuoqqIrgxfpGrvML
Since it was a Many to Many Replationship between Table 1 and Table 2 ( which we want to avoid), you will need to do a merge query in Power Query.
https://radacad.com/choose-the-right-merge-join-type-in-power-bi
Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
