cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
GZ
Regular Visitor

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):

GZ_0-1664377645129.png

 

 

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?

1 ACCEPTED SOLUTION

Hi @GZ ,

 

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.

View solution in original post

4 REPLIES 4
pottsbi
Frequent Visitor

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.

GZ
Regular Visitor

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? 

Hi @GZ ,

 

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.

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. 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors