Help with a simple calculation please

[mcarangelo]

I apologize in advance, but all I'm trying to do is calculate the difference between 2 columns and display the result of the calcuation. I've searched documentation, user posts etc. and nothing that clearly and consisely provides help. To be honest, it's not the most intuitive application in general. 

That being said, I attached a screenshot which shows the two fields I would like to calculate the difference and a screenshot of the "difference" field I attempted to create.

I thought there was an option to attach a file but I dont see anything. There appears to be link for videos, pictures and formatting but no basic attachment option.

Thank you 

[Anonymous]

Hi @mcarangelo ,

Thanks for the reply from Greg_Deckler .

This is sample data that I created myself:

SalesA	SalesB
100	80
200	90
300	110
400	280
500	340

Create a measure:

Measure = MAX('Table'[SalesA]) - MAX('Table'[SalesB])

The page effect is as follows:

The pbix file is attached.

You can share an image or file by uploading it to a cloud storage service (e.g. OneDrive, Google Drive) and sharing the link.

Please do not log in to your account in Power BI Desktop when uploading pbix files.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[mcarangelo]

The fields are in (2) different tables. Would I use the 'measures' option?

[Anonymous · Accepted solution]

Hi @mcarangelo ,

Thanks for the reply from Anand24 .

Maybe you can try to convert Direct Query mode to Import mode.

On the status bar bottom right corner, there's a text that reads "Direct Query: On (Click Here To Change)", if you click that and pick Import, your model should start importing.

Note that this is currently a one way trip. i.e., an Import Model cannot be converted back to DirectQuery.

After I established the relationship between the two tables, it worked normally.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[mcarangelo]

Hello - Converting to import mode unfortunately is not an option. The report is connected to a Snowflake database.

[Anand24]

@mcarangelo ,

Measure would be ideal since you are using fields from 2 separate tables. Even if it was from a single table, measure would be ideal since it doesn't increase data model size. 

One important point is that both your tables should have a relationship between them.

Can you attach a screenshot of your model view? Also, it will be great if you have sample data for both the tables.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[mcarangelo]

[mcarangelo]

Here is a view of the report. The fields in yellow require the calculation with the result expected in green. Also, do the fields in blue require the same values in order for this formula to work?

[mcarangelo]

Will using 'measure' result in a new column with the calculation result? I attached a screenshot of the model with the fields highlighted in yellow.

[mcarangelo]

I used the suggestion above (to create a measure) and encountered an error message: "This query contains transformations that cant be used for direct query. " I included a screenshot below. 

[Greg_Deckler]

@mcarangelo As a calculated column it should be:

Column = [Column 1] - [Column 2]

If a measure: 

Measure = SUM('Table'[Column 1]) - SUM('Table'[Column 2]

If instead these are columns in a visual that are already in a visual and they are measures:

Measure = [Measure 1] - [Measure 2]

[mcarangelo]

Hi Greg - I'm using the first option you suggestion and it results in "fix data" messages. Can you please advise why its not working?