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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Clustered column chart (percentage)

Hi All!

In the below graph, I want to show 'Achieved Qty' as a percentage of 'Target'.

Eg:- For product D, I want to show 70% (i.e. 210/300) in the graph.

Is there any option to do this or is there any other custom visual to do this?

Thank you!

Untitled 4.jpg

 

2 ACCEPTED SOLUTIONS
skasper
Responsive Resident
Responsive Resident

Hi @Anonymous,

 

you need to define your 'Achieved%' as a measure using e.g.:

 

Achievement% = CALCULATE(SUM('Sales Table'[Achieved Qty]);'Sales Table'[SM Code])/CALCULATE(SUM('Sales Table'[Target]);'Sales Table'[SM Code])

 

The measure needs to be formatted as '%'.

 

The resulting chart looks like this:

Clipboard01.jpg

 

The visual settings:

Clipboard02.jpg

 

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.

View solution in original post

skasper
Responsive Resident
Responsive Resident

That's because you added a 'column', not a 'measure'. Makes a difference - as you can see. Make sure to add 'measure':

 

Clipboard01.png

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.

View solution in original post

10 REPLIES 10
atulanky
Frequent Visitor

I have the same requirement but struggling with it.

Untitled.png

As shown in the below image created one measure to calculate the hours % based on BMI manhours against the BMcD manhours  

Eg. BMI manhours in green 40135 and BMcD manhours in black 1025307, and want to display 3.91% on line chart.

skasper
Responsive Resident
Responsive Resident

Hi,

 

you can do this, if you are ok with showing the percentage values as a line. To do so select the visual type 'Line and Clustered Column Chart'

 

Clipboard02.jpg

 

You will then get a chart like this with the percentages on a second Y-Axis:

 

Clipboard01.jpg

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
v-ljerr-msft
Employee
Employee

Hi @Anonymous,

 

I don't think it is possible to do it currently. However, you can create a new measure to calculate the percentage of 'Target' and show it on the Tooltip. When hovering over a data point on the visualization, the percentage of 'Target' will be shown on the Tooltip like below.

 

Regards

Anonymous
Not applicable

Dear @v-ljerr-msft and @skasper,

Thank you for your valuable suggestions. But my actual situation is like this:

Untitled.jpg

I want to visualize each Sales Manager's (ABC and XYZ) Achieved Qty, Target and Achieved Qty as a percentage of Target.

Eg: For the Sales Manager XYZ, Achieved% should be (150+100)/(200+150) = 71.4%

But in both 'Tooltip' method and 'Line and clustered column chart' method, it shows as 141.67% (i.e. 75%+66.67%).

Untitled 2.jpg

Is there any suggestion to do this please?

 

skasper
Responsive Resident
Responsive Resident

Hi @Anonymous,

 

you need to define your 'Achieved%' as a measure using e.g.:

 

Achievement% = CALCULATE(SUM('Sales Table'[Achieved Qty]);'Sales Table'[SM Code])/CALCULATE(SUM('Sales Table'[Target]);'Sales Table'[SM Code])

 

The measure needs to be formatted as '%'.

 

The resulting chart looks like this:

Clipboard01.jpg

 

The visual settings:

Clipboard02.jpg

 

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
Anonymous
Not applicable

Dear @skasper,

Thank you for your reply. I tried your expression. But it gave an error. I think the expression doesn't identify the semicolon mark. Please see below:

Untitled 3.jpg

skasper
Responsive Resident
Responsive Resident

Yes, that's possible. Replace it with whatever is your default delimiter (aka 'list separator'). This depends on your regional settings.

 

For me they look like this:

2016-10-28.png

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
Anonymous
Not applicable

Thank you for your quick reply. Now it gave the same old answer 😞

Please see below:

 

 

Untitled 4.jpg

 

Untitled 5.jpg 

 

skasper
Responsive Resident
Responsive Resident

That's because you added a 'column', not a 'measure'. Makes a difference - as you can see. Make sure to add 'measure':

 

Clipboard01.png

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
Anonymous
Not applicable

Now it's working. Thank you very much @skasper 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.