how to retrieve selected values from another table (with no relationship)

Hi all, I have 2 tables : Sales and Sales BR. My purpose is to fill Sales BR table with selected values from Sales table. Also i want to make some calculations.. There is no relationship betwe...

Need Help

Anonymous

5 years ago

cs141005

Here are the steps you can follow：

1. Enter the Power query through Transform data, add Index to the two tables respectively, and select Add column – Index Column.

2. Create calculated column.

Q1 =
IF(
    'Sales BR'[Index] in SELECTCOLUMNS('Sales',"1",'Sales'[Index]),
    SUMX(FILTER(ALL('Sales'),'Sales'[Index]='Sales BR'[Index]),[Q1]),
    DIVIDE(
        SUMX(FILTER(ALL(Sales),'Sales'[type]="new users"),[Q1]),
        SUMX(FILTER(ALL(Sales),'Sales'[type]="users"),[Q1])
    ))

Q2 =
IF(
    'Sales BR'[Index] in SELECTCOLUMNS('Sales',"1",'Sales'[Index]),
    SUMX(FILTER(ALL('Sales'),'Sales'[Index]='Sales BR'[Index]),[Q2]),
     DIVIDE(
        SUMX(FILTER(ALL(Sales),'Sales'[type]="new users"),[Q2]),
        SUMX(FILTER(ALL(Sales),'Sales'[type]="users"),[Q2])
    ))

3. Result:

Best Regards,

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

how to retrieve selected values from another table (with no relationship).pbix33 KB

VahidDM

Super User

5 years ago

cs141005

You can use "Replace Values" in the power query to change those line names, and you don't need to have another table (even if you need another table, you can create a duplicate table and change the values there).

check these links:

https://support.microsoft.com/en-us/office/replace-values-power-query-28256517-f1e9-4dc3-832f-45786e9cf721

https://yodalearning.com/tutorials/learn-how-replace-values-power-query/

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

Appreciate your Kudos !!