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

    Hallo, and thank you for your response and example given,

    i was trying to implement this and i am not sure how you created the index. 

    it is not a measure i see. 

     

     

  • 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.

  • marial16's avatar
    marial16
    Helper II
    4 years ago

    I finally managed to create the calculated row, however i was wondering if this solution would work if i had more rows in the same table or is it a best practice to create another table?

    example :

    Own Staff Contractors 
    157171
    141119
    0.8980.696
      
      
    284163
    1.8090.953

     

    where 1.809 is the result of 284 / 157

    And a last question of mine would be if i can use an excel file (calculations included) in Power BI without having to create all those measures. 

    I had decided to organise the data in SharePoint Lists. However i am not sure if it is the best practice.

    Thank you