Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors