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
Icon for Community Support rankCommunity Support
7 years ago

rogeriosouzax,

 

Seems like the slicers are both multi-selection slicers in your two screenshots, to be general, you may modify your measure like pattern below and try again:

Medida =
DIVIDE (
    COUNT ( Consulta1[Intervalo] );
    CALCULATE ( COUNT ( Consulta1[Intervalo] ); ALLSELECTED ( Consulta1 ) )
)

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

    v-yuta-msft,

    Thank you for feedback :)

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

     

    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.

     

    Best regards,

     

    Roger.

    • v-yuta-msft's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity 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