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

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors