Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 relationshi...
  • v-yinliw-msft's avatar
    v-yinliw-msft
    4 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.

pottsbi's avatar
pottsbi
Frequent Visitor
4 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
    4 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
      4 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you very much! This does indeed solve my issue.

         

        I have multiple financial reports that include overlapping tags (e.g. Assets). 

         

        Ultimately, I would like to efficiently calculate performance indicators for multiple reports. I want to import let's say 10 reports with overlapping tags, put them into relationship and visualize performance indicators that use the same formula and tags from each respective report.

         

        I got my formula now thanks to you but it isannoying that I have to create this measure for each report.