Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have created a new column which uses the Switch function to negate a value under certain conditions.
hi @JPRogers - Can you please confirm if INVENTORY_VALUE is a DAX formula or a calculated column?
It looks like the report visualization is setup to display 2 decimals but the measure itself maybe rounding off the result. Can you please check and confirm that the measure is setup as also setup as a Decimal number
Another point to note is that the visual level formatting overrides the measure level formatting.
Also if possible can you share a sample of the report in question post removing any sensitive data.
Hope this helps!
Proud to be a Super User!
Hi Sumanth,
Tahnk you for getting n touch, I am fairly new to PBI so forgive me if I havent answered your question fully.
Below is a screen shot of the screen showing the new column and the formula.
INVENTORY_VALUE is a new column added to the table. It is a calculated column. The data is from a Snowflake Database table and I am using Direct Query. The formula that defines the column negates the value in another column in the table from Snowflake under certain conditions.
The Snowflake column is RETAIL_VALUE, if the RMS Code is of a certain value, I want to negate value in the RETAIL_VALUE column and the result to be held in the INVENTROY_VALUE new column. New column is not in the databse but calculated in PBI.
Both the column from the database and the calculated column in PBI are Decimal Numbers and have two decimal places.
The column determining if the value should be negated or not, is the RMS_CODE, also from Snowflake, and is a text column.
Below is a screen shot of the dashboard, showing the attributes of the INVENTORY_VALUE column.
1. showing the column added to the table.
2, showing the Data Type as Decimla Number
3. showing the name of the column, INVENTORY_VALUE
4. showing the format, as Decimal Number with 2 Decimla Places
5, showing that I want to Sum it in the visualisation.
I cannot show the data in the visialisation, as its sensitive data, I'm sure you will understand, however, it shows as -23.00
hi @JPRogers - I was able to create a sample report and replicate the steps you are performing and able to create the calculated column as expected - I just keyed in some dummy data for the scenario you had explained
And the format of the calculated column seems to be carrying through to the visuals as expected. Please see screenshots below:
Also attaching the .pbix file for reference. Hope this helps!
Proud to be a Super User!
Thank you Sumanth,
your solution and mine look identical as do the attributes of the columns, but I dont know why you can get the decimal places and mine rounds up.
Thanks for helping.
regards
John
hi @JPRogers - I am not sure if there is a way you can setup a local/offline version of the report and share a pbix file without sensitive data for my review - but if the solution is similar to what I have setup I am not sure what the issue could be.
But definitely it looks like the value is getting rounded up before getting to the visual and hence you see the decimal places in the visual but its rounded up.
Hope that helps!
Proud to be a Super User!
@JPRogers Can you upload a sample report demonstrating the issue and share via OneDrive please?
What's the data type of [RMS_TRANSACTION_CODE], not sure but this may be part of the issue?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Alison,
Thank you for your reply, the column RMS_TRANSACTION_CODE is a text column. Do you think that it is influencing the result adversely? How do I get round that?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |