Forum Discussion

marial16's avatar
marial16
Helper II
4 years ago
Solved

Add calculated Row in table

Hallo,  i import 3 tables from SP to help me create a KPI graph Can i add a row at the table in BI to calculate (division) of 2 rows above? (Last Row)   Example Own Staff  Contractor 157 ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, marial16 

    You could create measures and change the Total field to the division you want.

     

    Like this:

    _Own Staff = 
    IF(
        ISINSCOPE('Table'[Index]),SUM('Table'[Own Staff ]),
        DIVIDE(
            CALCULATE(SUM('Table'[Own Staff ]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
            CALCULATE(SUM('Table'[Own Staff ]),FILTER(ALL('Table'),'Table'[Index]=MAXX(FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])),[Index])))
        )
    )

     

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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

     

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

Hi, marial16 

You could create measures and change the Total field to the division you want.

 

Like this:

_Own Staff = 
    IF(
        ISINSCOPE('Table'[Index]),SUM('Table'[Own Staff ]),
        DIVIDE(
            CALCULATE(SUM('Table'[Own Staff ]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index]))),
            CALCULATE(SUM('Table'[Own Staff ]),FILTER(ALL('Table'),'Table'[Index]=MAXX(FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])),[Index])))
        )
    )

 

Result:

Please refer to the attachment below for details. Hope this helps.

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

 

marial16's avatar
marial16
Helper II
4 years ago

Ok i manged to add an index column, but i am getting an error when i check the measures and try to create the table visualization:

"The function SUM cannot work with values of type string"

In the Sharepoint list the column type is number.

What am i missing?

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, marial16 


    I am adding an index column in Power Query. If the column type is not numeric, then you can change the type to numeric in PowerQuery or on the desktop. Then the SUM function will work properly.

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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