Forum Discussion

rogeriosouzax's avatar
rogeriosouzax
Regular Visitor
7 years ago
Solved

Problem calculating percentage total - bar chart

Hello guys!   I'm new in Power BI :) I'm trying to create a bar chart to display my data but it's working partially.   I create a new measure to calculate the percentage of total. Follow my formu...
Need Help
Show and Tell
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    rogeriosouzax

     

    Hi, try with this:

     

    Medida = DIVIDE(SUM(Table1[Valor]),CALCULATE(SUM(Table1[Valor]),ALLSELECTED(Table1[Faixa])))

    Regards

     

    Victor

     

v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

rogeriosouzax,

 

 I got the total percentage, if I sum all values referent A and B I have 99,99% and it's right. But I need that the sum of all B columns displays 100%(or almost it) and all A columns displays 100%(or almost it).

 

How could I do that? Is it possible? I tried the last pattern with ALLEXCEPT but using some filters the value displayed is incorrect.

 



I tried the pattern that you suggested. It works but not like I expected.

 

Could you clarify more about "display 100%"? If possible, could you please share some sample data?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rogeriosouzax's avatar
rogeriosouzax
Regular Visitor
7 years ago

Hello v-yuta-msft,

 

The 100% represents the sum of all columns related to a Product (Produto).

 

I created a new scenario to simulate the problem. I reduce the number of fields to make it easy.

Dashboard Simulation

The dataset that I used is:

FamiliaProdutoBRRegionalFaixaValor
HomeBlue-RayBR1SPA1
HomeTVBR2MTB1
HomeBlue-RayBR3ALC1
HomeTVBR1SPD1
HomeBlue-RayBR2MTE1
HomeTVBR3ALA1
HomeBlue-RayBR1SPB1
HomeTVBR2MTC1
HomeBlue-RayBR3ALD1
HomeTVBR1SPE1
HomeBlue-RayBR2MTA1
HomeTVBR3ALB1
HomeBlue-RayBR1SPC1
HomeTVBR2MTD1
HomeBlue-RayBR3ALE1
HomeTVBR1SPA1
HomeBlue-RayBR2MTB1
HomeTVBR3ALC1
HomeBlue-RayBR1SPD1
HomeTVBR2MTE1
HomeBlue-RayBR3ALA1
HomeTVBR1SPB1
HomeBlue-RayBR2MTC1
HomeTVBR3ALD1
HomeBlue-RayBR1SPE1
HomeX-BoxBR1SPA1
HomeX-BoxBR2MTB1
HomeX-BoxBR3ALC1
HomeX-BoxBR1SPD1
HomeX-BoxBR2MTE1
HomeX-BoxBR3ALA1
HomeX-BoxBR1SPB1
HomeX-BoxBR2MTC1
HomeX-BoxBR3ALD1
HomeX-BoxBR1SPE1

 

I’m using two bar charts to check and to explain the scenario.

 

The barchart below I use just to check the values, notice that if you sum de percentage the value is 100% (23,08% + 15,38% + 23,08% + 15,38% + 23,08%). I also filter this chart by the “Produto” that I need to check.

Chart to verify percentage

Here is how I configured it.

Chart 1 - set up

This second barchart represents the comparison between my “Produtos”, I use a measure to calculate the value.

The measure (“medida”) formula is: Medida = DIVIDE(COUNT('chamado-ms'[Faixa]);CALCULATE(COUNT('chamado-ms'[Faixa]);ALLEXCEPT('chamado-ms';'chamado-ms'[Familia];'chamado-ms'[Produto];'chamado-ms'[BR];'chamado-ms'[Regional])))

Chart 2 - Comparison between "Produtos"

Here is how I configured chart 2.

Chart 2 - set up

Now I will show two scenarios, the first one is when it works properly and the second one is when I get the unexpected behavior.

 

Scenario 1: Filtering only by “Produto”.

scenario 1

The values related to “Produto” Blue-Ray are equal in both charts, if we sum the values we get the value 100% (23,08% + 15,38% + 23,08% + 15,38% + 23,08%).

The value when we sum the second product “TV” is also correct (100,01%).

 

Scenario 2: Filtering by “Produto” and “Regional”.

Scenario 2

When I add this filter, my measure “medida” calculates the value incorretly.

 

How could I fix it? I have no idea about the reason of this behavior :(

 

Best regards

 

Roger

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    7 years ago

    rogeriosouzax

     

    Hi, try with this:

     

    Medida = DIVIDE(SUM(Table1[Valor]),CALCULATE(SUM(Table1[Valor]),ALLSELECTED(Table1[Faixa])))

    Regards

     

    Victor

     

    • rogeriosouzax's avatar
      rogeriosouzax
      Regular Visitor
      7 years ago

      Hi Vvelarde,

       

      It works!!! Thank you!

      Just a point that I want to share. When I try to "Sort by column" using a different column  I have the following behavior.

      Using a different field to Sort By Column

      All columns displays 100%, it's crazy, isn't it? kkk

      Well, it's working as I need now (I will use the default "sort").

       

      Thank you v-yuta-msft and Vvelarde for help!

       

      Best regards,

       

      Roger