Latest Enterprise DNA Initiatives

Showing values in %


This is probably a simple one for most of you but I have hit a serious mental block on how to do this.

I have attached a PBI file and a PPT as well to demonstrate the issue. I want to create 2 graphs using the data from the table DATA. This table has 3 fields - Assessment Date *(which is a year - whole number format), M or R (showing whether its Mandatory or Recommendation) and the Reason field (this has 8 categories e.g. DOcumentation, SOftware, Equipment etc)

  1. The first graph I want is a line graph in which the x-axis is the Assessment Year, the y-axis should show the count of Reason (using Reason as a legend i.e. there is a line for each reason type) . I can show it as a count by Reason type but I want to show it as a percentage of the total count of reasons for that year.

  2. The second is a cluster chart grouped by the Reason type and sub-broken into a count by year (which is the legend in this case). Again I want to show a data label of % as a total count FOR THAT YEAR.

I have attached the PPT which shows the graphs in Excel.

I know this is probably very simple for you guys but I am completely stumped on this. ANy help will be greatly appreciated.

Sample.pbix (436.6 KB)
Example.pptx (114.0 KB)
ind regards

Hello Prem,
Is this what you need?

If the answer is “Yes”, then these are the steps:

  1. In the “Data” table, add a Column
    ReasonsNum = CALCULATE(COUNTROWS(Data),Data[Assessment year])
  2. Create a Measure to identify the Total:
    Reasons Total = SUMX(Data,Data[ReasonsNum])
  3. Filter the Reasons so it will return the SUM for the Year
    ReasonsFiltered = CALCULATE(SUM(Data[ReasonsNum]),ALLEXCEPT(Data,Data[Assessment year]))
  4. Divide the measures
    Count of M & R % = DIVIDE([Reasons Total],[ReasonsFiltered])
  5. Click on the newly created Measure (Count of M & R % ) and change the format to percentage

After that , drag and drop the measure in the Values field of your Clustered Column chart and Line Chart

Hope this helps.


Hey Sorin

Thank you so much for this - it works like a charm

Kind regards

1 Like