Forum Discussion

Worldbreaker's avatar
Worldbreaker
Helper I
9 years ago
Solved

Addition, sum logic in Power BI

I am having issues with how Power BI sums up my data.   basically, here is an example of my data: Fruits Basket 1 Basket 2 Basket 3 total Fruit Additional Fruits on basket Apple     ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Worldbreaker,


    I try to reproduce and get the desired result, please review the following steps.

    1. Click "New Table" under Modeling on Home page, type the following formula.

    Table = UNION(UNION(SELECTCOLUMNS(Table1,"Basket",Table1[Basket 1]),SELECTCOLUMNS(Table1,"Basket",Table1[Basket 2])),SELECTCOLUMNS(Table1,"Basket",Table1[Basket 3]))

    2. Create another new table based on the table above.

    Table 2 = SUMMARIZE(FILTER('Table','Table'[Basket]<>BLANK()),'Table'[Basket],"Additional",COUNTA('Table'[Basket]))



    3. Create a relationship between your table, and the new 'Table 2'.



    4. Create a table visual, select the sum of total fruit, the 'Table 2'[Additional] but don't summarize, you will get the right result.



    Thanks,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Worldbreaker,


I try to reproduce and get the desired result, please review the following steps.

1. Click "New Table" under Modeling on Home page, type the following formula.

Table = UNION(UNION(SELECTCOLUMNS(Table1,"Basket",Table1[Basket 1]),SELECTCOLUMNS(Table1,"Basket",Table1[Basket 2])),SELECTCOLUMNS(Table1,"Basket",Table1[Basket 3]))

2. Create another new table based on the table above.

Table 2 = SUMMARIZE(FILTER('Table','Table'[Basket]<>BLANK()),'Table'[Basket],"Additional",COUNTA('Table'[Basket]))



3. Create a relationship between your table, and the new 'Table 2'.



4. Create a table visual, select the sum of total fruit, the 'Table 2'[Additional] but don't summarize, you will get the right result.



Thanks,
Angelia