Showing values in %

[prembij]

Hi

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.

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

[sorinlinx]

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.
Sorin

[prembij]

Hey Sorin

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

Kind regards
Prem