Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to calculate two single values from table(s)?

Hi everyone,

 

I extracted data from a financial report as a table and imported it into Power BI. The structure of the table allows me to import and compare multiple reports by creating relationships. Overall, this is great and I am able to visualize that data. Here is a snippet of my data in a table (just a single report):

 

 

My issue now is that I want to calculate financial numbers like ROCE etc. In order to do that I need to filter the value of the label (such as "Assets2021" ) and for example divide it by another value of a label. Naturally, I'd like to do that for multiple reports.

 

I understand that I need the Divide-function. I am trying to create a filter for each label as a measure in Power BI but this idea does not work.

 

Do you have any solutions?

  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi Anonymous ,

     

    Measure couldn’t accept to return a table with multi columns.

    If you want a measure, you need add your expression before filter.

    Measure:

    Assets2020 = CALCULATE(SUM('Finale Fact List'[Value]),FILTER('Finale Fact List',[Label1]="ifrs-full:Assets31.12.2020")

     

    To avoid misunderstandings, you could elaborate on your expected results and calculation logic.

     

    Hope this helps you.

     

    Best Regards,

    Community Support Team _Yinliw

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

4 Replies

  • pottsbi's avatar
    pottsbi
    Frequent Visitor
    3 years ago

    You could create a measure for the numerator and denominator each with the respective filters. Then reference those in a third measure either directly in the divide statement or reference them first as variables and use the variables in your DIVIDE()

    Another option is to use a DIVIDE() with two CALCULATES() and add the respective filters as parameters in the divide. It may require first using an ALL() then your filter depending on what other entities filter that table.

    If you think in the future you'll need to use either of those numerator or denominator values again in different context I would create separate measures for them.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Great! I think we are on the same page since that's my intended solution. I already tried using the following measure:

      Assets2020 = FILTER('Finale Fact List', [Label1]="ifrs-full:Assets31.12.2020")
       
      This error message is displayed:  "The expression refers to multiple columns. Multiple columns cannot be converted to one scalar value."
       
      I actually don't know what to think of that. The names (table, column, label) in that measure are correct I assume. Did I do any mistakes elsewhere? (You can relate to my example by looking at the snippet I shared.)
       
      I guess I forgot to reference the "value"-column? 
      • v-yinliw-msft's avatar
        v-yinliw-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Anonymous ,

         

        Measure couldn’t accept to return a table with multi columns.

        If you want a measure, you need add your expression before filter.

        Measure:

        Assets2020 = CALCULATE(SUM('Finale Fact List'[Value]),FILTER('Finale Fact List',[Label1]="ifrs-full:Assets31.12.2020")

         

        To avoid misunderstandings, you could elaborate on your expected results and calculation logic.

         

        Hope this helps you.

         

        Best Regards,

        Community Support Team _Yinliw

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