Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Raul
Post Patron
Post Patron

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
Vvelarde
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

Vvelarde
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

8 REPLIES 8
kcoutu
New Member

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
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
Anonymous
Not applicable

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?

Vvelarde
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

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!

 

Oh YES!!!!,

That is! Thank you very much @Vvelarde.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.