cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rnadathur Frequent Visitor
Frequent Visitor

percentage vales on the y-axis in a graph

I have a column A which needs to be divided by column B and the resultant value needs to be a percentage displayed in a graph

For example : the formula should be like this % P = (A/B) * 100 

how do i create the dax formula for the  resultant measure 

the users will filter it based on the month and year and should be able to see %P based on the month and year in the graph 

 

8 REPLIES 8
Ross73312 Super Contributor
Super Contributor

Re: percentage vales on the y-axis in a graph

A simple way would be to create 3 measures. The first two would be the sum of Column A and B respectively.  Something like:

 

SumColumnA = sum('TableName'[ColumnA])

 

Then you can create the measure:

PercentP = [SumColumnA] / [SumColumnB] * 100

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


rnadathur Frequent Visitor
Frequent Visitor

Re: percentage vales on the y-axis in a graph

Hi Ross 

I want a row by row percentage. Your solution will give the percentage for the entire column 

P = A/B * 100 should give me the percentage for each row of A and B 

Ross73312 Super Contributor
Super Contributor

Re: percentage vales on the y-axis in a graph

If you want a Row by Row version, create a custom column and use a formula:

PercentP = [ColumnA] / [ColumnB]

If this row by row version needs Column B to be the entire column, replace that [ColumnB] part of my formula to reference this measure:

SumColumnB = Calculate(
    Sum('Table1'[ColumnB]),
    ALL('Table1')
)

This measure could be updated to use ALLEXCEPT or ALLSELECTED depending on what splicers you wish to take into account.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


rnadathur Frequent Visitor
Frequent Visitor

Re: percentage vales on the y-axis in a graph

Hi Ross,

 

Thanks a lot for your reply

I created a  calculated column which would give me the percentage 

when i try to include it in a  line chart the percentage gets summarized and the legend shows the "SUM of meaure" and i dont see an option to edit that to an appropriate name 

I am attaching a screenshot of my report which will help you get an inisght of what i am trying to achieve Screenshot.JPG

Ross73312 Super Contributor
Super Contributor

Re: percentage vales on the y-axis in a graph

Click on the visual and look under the Visualisation menu on the right of your screen.  There is a paintbrush section where you will have the Toggle Option to display the Title or not.  Expanding this there is an area called "Title Text" you can update.

 

Is that what you were hoping to change?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


rnadathur Frequent Visitor
Frequent Visitor

Re: percentage vales on the y-axis in a graph

Hi Ross 

 

I am looking to change the legends "SUM OF %Timely" to just "Timely" and "SUM OF %Untimely" to just "Untimely"

Ross73312 Super Contributor
Super Contributor

Re: percentage vales on the y-axis in a graph

I tend to use a lot of measures, so a straight forward solution could be to create a Measure that does the sum for you, then when it appears in your Legend it will have the nicer name?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Moderator v-qiuyu-msft
Moderator

Re: percentage vales on the y-axis in a graph

Hi @rnadathur,

 

The Values scetion for the line chart need to be aggregated. In your scenario, as you create a calculated clumn and place it in Values property, it will be set with one kind of aggregate function, eg: SUM. So the legned name is changed with that  aggregate function.

 

I would suggest you to create a measure instead of the measure with below DAX: 

 

Percentage=  DIVIDE(SUM([ColumnA]),SUM([ColumnB]))

 

then set Format as Percentage for this measure.

 

q4.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 263 members 2,906 guests
Please welcome our newest community members: