cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
melih
Regular Visitor

Calculating in visualisation

Hey,

 

I want to calculate with the data in the table in 'Report'. The data in the table is grouped by through the visualisation, so I can't use the data. How can I use the data in the table to calculate a new column? 

 

melih_0-1652966791750.png

 

I want a new column 'Accuracy'. This through 'Capacity' divided by 'Total Forecast'. In conclusion I am not able to use this in the data. 

 

Thanks in advance. 

11 REPLIES 11
melih
Regular Visitor

Hey,

 

I want to calculate with the data in the table in 'Report'. The data in the table is grouped by through the visualisation, so I can't use the data. How can I use the data in the table to calculate a new column? 

 

melih_0-1652969060606.png

 

I want a new column 'Accuracy'. This through 'Capacity' minused by 'Total Forecast'. In conclusion I am not able to use this in the data. 

 

Thanks in advance. 

SpartaBI
Community Champion
Community Champion

@melih what do you mean you are not able to?
How does the desired report looks like?
Did you try to write this measure:
Accuracy = SUM('Table'[Capacity]) - SUM('Table'[Total Forecast])



2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

melih
Regular Visitor

Didn't work man. The result needs to be 48-11 = 37 and not 15. Do you got another idea?

melih_0-1653043958505.png

 

SpartaBI
Community Champion
Community Champion

Capacity and total forcast are measures?

 

melih
Regular Visitor

melih_0-1653044363602.png

 

SpartaBI
Community Champion
Community Champion

ok, so thery are columms and you did implicit measures on them. 
How are they aggregated in you visual? SUM / AVERAGE?
If it's not just sum then you need to create explicit measures for them and them just reduce 1 measure from the other

melih
Regular Visitor

HEy @selimovd ,

 

Thank you. Actually I would like to minuse them. How can I fix that?

Hey @melih ,

 

as far as I would guess, the subtraction has to happen on a row level in the table. For that reason you have to use SUMX, which iterates over each row.

The following measure would go row by row and subtract forecast from capacity and build a sum at the end:

Accuracy = SUMX ( myTable, myTable[Capacity] - myTable[Total Forecast] )

 

Is that what you need?

If not, could you give me an example how the result should look like?

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

melih
Regular Visitor

Hey @selimovd ,

 

It didn't work. For example the first result of the row gives me 15. But 48-11 should be 37. 

melih_0-1653041814692.png

 

Hey @melih ,

 

so you don't want it on a row level 😉

In that case you can just subtract:

Accuracy = SUM ( myTable[Capacity] ) - SUM ( myTable[Total Forecast] )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

selimovd
Super User
Super User

Hey @melih ,

 

to add a "new column" to the visual, you have to create a measure and add it to the visual.

The measure is then executed in every row with the context of the row.

 

The following measure should do the job:

Accuracy = DIVIDE ( SUM ( myTable[Capacity] ), SUM ( myTable[Total Forecast] ) )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors