cancel
Showing results for
Did you mean:
Helper V

## Calculating Percentage of total column for bar chart

Hello,

I have a matrix with this values:

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:

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:

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

Thank you?

3 ACCEPTED SOLUTIONS
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
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
Helper V

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!

7 REPLIES 7
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
Frequent Visitor

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

Helper V

Hi @Vvelarde,

The result is this:

For your information, this is the model relationship:

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

Any idea?

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
Helper V

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.

Helper V

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!

Helper V

Oh YES!!!!,

That is! Thank you very much @Vvelarde.

Announcements

#### Happy New Year from Power BI

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