Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

DAX Measure to link and Query optimization

  • 1.  DAX Measure to link and Query optimization

    Posted Jan 16, 2022 10:47 PM

    Hello everyone,

    So, I have been tasked with automating some reports for my organization. I'm relatively new to DAX so I request help with this one. Apologies if this goes very long.

    I have a sales table like this:

    Post image

    And a stock table like this:

    Post image

    I need to prepare a top 30 sales report as the main report. And for reconciliation, the rest of the products should be numbered as 31 and named as Others. So what I did is:

    1. Took the data via Power Query

    2. Created a separate table for Top 30 with an index (Let's call it Index Table)

    3. Merged with the existing tables shown above

    4. Changed the blanks (Products other than the Top 30) with Rank as 31 and Name as Others

    5. Linked the Index table with the above 2 tables via relationships in Power Pivot.

    The issue is, I need a subsidiary report with the top 5 supplier sales of the top 30 units along with the stock present with the respective suppliers. Below image for reference:

    Post image

    I took the first two columns from Index Table and the rest from the linked tables. However, I couldn't get the stock values as intended. It cites something related to relationships. I couldn't create any other relationship related to the supplier due to many duplicates present under "Others".

    It's okay if this report doesn't have the 31st product. Top 30 is fine. However, I don't want to create a separate file for this altogether.

    I want to know whether I could write any DAX measure to get the stock values. Also, is there any way I could optimize the way I arrived at the top 30 products?

    Thank you.

    Arun Subramanian