topic Re: How to calculate two single values from table(s)? in DAX Commands and Tips

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

Anonymous — Wed, 28 Sep 2022 15:11:41 GMT

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?

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

pottsbi — Wed, 28 Sep 2022 15:17:19 GMT

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.

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

Anonymous — Wed, 28 Sep 2022 15:33:46 GMT

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?

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

v-yinliw-msft — Thu, 29 Sep 2022 09:52:33 GMT

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.

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

Anonymous — Thu, 29 Sep 2022 16:25:27 GMT

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.