Calculating Percentage of total column for bar chart

[Raul]

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?

[Vvelarde · Accepted solution]

@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

[Vvelarde · Accepted solution]

@Raul

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

Let me know whta result you obtain.

[kcoutu]

I was able to use the formula referenced above, however the results totaled 100% together - where I'm looking to have each year equal 100%. How can I modify?

=DIVIDE(COUNT('Leave-Program'[Leave Reason]),CALCULATE(Count('Leave-Program'[Leave Reason]),ALLEXCEPT('Leave-Program','Leave-Program'[Begin Date])))

[Vvelarde · Accepted solution]

@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

[Anonymous]

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

[Raul]

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?

[Vvelarde · Accepted solution]

@Raul

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

Let me know whta result you obtain.

[Raul]

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.

[Raul · Accepted solution]

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!

[Raul]

Oh YES!!!!,

That is! Thank you very much @Vvelarde.