cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Calculating Percentage of total column for bar chart

Hello,

I have a matrix with this values:

 

Matriz.JPG

Where I calculate the percent over total of a column for each year with the option "Show value as - Percent of column total" (the field on the Values box is Count of StoreKey). This is correct but I want to show this percent for a bar chart. If a change the visualization to a bar chart, the result is this:

 

Chart.JPG

But this is not correct, because I want this percent of column total in the values of the chart, not the percent of the gran total, like this:

 

Chart2.JPG

How can I make this chart? What should be the calculation measures?

Thank you?

 

 

 

 

 

 

3 ACCEPTED SOLUTIONS
Community Champion
Community Champion

@Raul

 

Hi, Try with this measure:

 

Measure =
DIVIDE (
    COUNT ( Table1[Storekey] ),
    CALCULATE ( COUNT ( Table1[Storekey] ), ALLEXCEPT ( Table1, Table1[Year] ) )
)

Let me know if works.

 

Victor

Lima-Peru




Lima - Peru

View solution in original post

Community Champion
Community Champion

@Raul

 

hi, please change the Data type--Format of the measure to Percentage and with 2 decimals.

 

Let me know whta result you obtain.




Lima - Peru

View solution in original post

I answer myself. Only I have to add on the measure formula the new filter,  like this:

 

Measure = DIVIDE ( COUNT ( Table1[Storekey] ), CALCULATE ( COUNT ( Table1[Storekey] ), ALLEXCEPT ( Table1, Table1[Year] ) , ALLEXCEPT(Table1, Table2[Country])) )

 

And that's it!

 

View solution in original post

7 REPLIES 7
Community Champion
Community Champion

@Raul

 

Hi, Try with this measure:

 

Measure =
DIVIDE (
    COUNT ( Table1[Storekey] ),
    CALCULATE ( COUNT ( Table1[Storekey] ), ALLEXCEPT ( Table1, Table1[Year] ) )
)

Let me know if works.

 

Victor

Lima-Peru




Lima - Peru

View solution in original post

Victor, I was having a similar issue as the Original Poster. Your solution worked great for me. Thanks for the help!

Hi @Vvelarde,

The result is this:

Chart3.JPG

For your information, this is the model relationship:

Model.JPG

 

Where FiscalYear is from Fecha table, ChannelDescription is from Canal table and the StoreKey is from Venta table.

Any idea?

Community Champion
Community Champion

@Raul

 

hi, please change the Data type--Format of the measure to Percentage and with 2 decimals.

 

Let me know whta result you obtain.




Lima - Peru

View solution in original post

I'm sorry but, one more question: if I put a filter on the report level (Country = China), the matrix change correctly but not the bar chart. What is wrong?
Thank you.

 

Captura.JPG

 

I answer myself. Only I have to add on the measure formula the new filter,  like this:

 

Measure = DIVIDE ( COUNT ( Table1[Storekey] ), CALCULATE ( COUNT ( Table1[Storekey] ), ALLEXCEPT ( Table1, Table1[Year] ) , ALLEXCEPT(Table1, Table2[Country])) )

 

And that's it!

 

View solution in original post

Oh YES!!!!,

That is! Thank you very much @Vvelarde.

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors