topic Re: Custom Formatted Measure in Pie Chart in DAX Commands and Tips

Custom Formatted Measure in Pie Chart

scabral — Sat, 15 Oct 2022 20:31:03 GMT

I created the following measure to custom format a value:

VAR SkipThousands =

IF([TIV] > 0, ROUNDDOWN ( DIVIDE ( LOG10 ( [TIV]), 3 ), 0 ))

VAR ValueFormatString =

SWITCH (

SkipThousands,

0, "$#,0", -- Integer number

1, "$#,0,.0K", -- Thousand

2, "$#,0,,.0M", -- Million

3, "$#,0,,,.0bn", -- Billion

"$#,0,,,,.0T" -- Trillion

)

RETURN

IF([TIV] = 0, "$0",

FORMAT([TIV],ValueFormatString))

This measure works fine in a table, but if I try to use it in a Pie Chart, i get the following error:

Can't Display Visual

Negative vlaues aren't supported and are being displayed as absolute values.

There are no negative numbers in the entire dataset for this measure. If I use just the underlying [TIV] measure, it works fine. It doesn't like the formatting for some reason. Is this a bug?

Scott

Re: Custom Formatted Measure in Pie Chart

Anonymous — Mon, 17 Oct 2022 02:46:45 GMT

Hi scabral ,

Have you tried going into the dataview and check if there are actuall negative values?

My tests did not reproduce your problem,can you provide some sample data or .PBIX files so that I can better help you solve the problem, please pay attention to protect your private data ,thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Custom Formatted Measure in Pie Chart

scabral — Mon, 17 Oct 2022 17:03:35 GMT

Hi Neeko Tang,

how to i upload a pbix file to the forum? I didn't see an option.

Re: Custom Formatted Measure in Pie Chart

Anonymous — Tue, 18 Oct 2022 06:20:18 GMT

Hi scabral ,

Please refer to this link: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Custom Formatted Measure in Pie Chart

scabral — Tue, 18 Oct 2022 12:41:12 GMT

https://app.box.com/s/csahwld2tyuaiq78u1tysero3u9htdce

Hi Neeko Tang,

I've uploaded and shared sample file to BOX and attached link.

There is just 1 table where I created a TIV measure to just sum up the Total Insured Value.

Now, I noticed that if i create the new measure called TIV Formatted with the code i listed above, it saves it as a text field and i cannot change it. If I use the attached pbix as the dataset and connect to it via another pbix and create the TIV formatted measure there, it allows me to set it to a number. Not sure why that is. But you cannot use text field in the values section of a pie chart, so you will have to use the attached pbix as a dataset and create another pbix to create the measure as a number and use it in the pie chart where you will see the error.