Forum Discussion

Aletom7's avatar
Aletom7
Regular Visitor
10 years ago

Problem with Ratios

Hello,

I'm pretty new here in the community, hope this is not a repeated post.

I have a problem showing ratios in powerbi.

I want the ratios be sorted by category and year in the same chart:

 

To calculate the ratios I used this measure:

Incidences = DIVIDE(CALCULATE(SUM('ESTRAZIONE DATI'[Costs (USD)]));CALCULATE(SUM('ESTRAZIONE DATI'[Revenues (USD)]));0)

 

I also calculated all the single ratios, but I did not find any way to visualize it in a chart like I want.

 

How can I do? 

Thank you all who will read this post and will try to solve my issue.

 

Ale

 

Need Help

10 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

    Hi Aletom,

     

    May i know do you want entile table percentage ? am i correct.

    • Aletom7's avatar
      Aletom7
      Regular Visitor
      10 years ago

      Yes, correct. I want to see percentages of the costs' categories (service, labor cost, rentals, etc) on revenues.

      • Baskar's avatar
        Baskar
        Resident Rockstar
        10 years ago

        Don't worry your prob got resolved,

         

        1. Go to your measure in chart there u can see the triangle in right side click that triangle ,

         

        2. there in top u can view "Quick Calc" and choose

         

        3. new pop up opened there in last box choose "Percentage of grand total"

         

         

        let me know if not done

         

         

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    Aletom7

     

    I’m not sure what your original dataset likes. I created some sample data as below. And you can get the ratios by creating a new table with following formula.

    If it is not your expected result, could you please show some sample data and the expected output to us?

    Ratio = 
SUMMARIZE (
    'ESTRAZIONE DATI',
    'ESTRAZIONE DATI'[Year],
    "Ratio_Service", DIVIDE (
        SUM ( 'ESTRAZIONE DATI'[SERVICE] ),
        SUM ( 'ESTRAZIONE DATI'[REVENUES] )
    ),
    "Ratio_LaborCost", DIVIDE (
        SUM ( 'ESTRAZIONE DATI'[LABOR COST] ),
        SUM ( 'ESTRAZIONE DATI'[REVENUES] )
    ),
    "Ratio_Rental", DIVIDE (
        SUM ( 'ESTRAZIONE DATI'[RENTAL] ),
        SUM ( 'ESTRAZIONE DATI'[REVENUES] )
    ),
    "Ratio_VDC", DIVIDE ( SUM ( 'ESTRAZIONE DATI'[VDC] ), SUM ( 'ESTRAZIONE DATI'[REVENUES] ) ),
    "Ratio_FDC", DIVIDE ( SUM ( 'ESTRAZIONE DATI'[FDC] ), SUM ( 'ESTRAZIONE DATI'[REVENUES] ) )
)

     

    Best Regards,
    Herbert

    • Aletom7's avatar
      Aletom7
      Regular Visitor
      10 years ago

      Thank you all for the solutions proposed.

      I think the problem is in the missing association between "Type" and the Incidences.

      The values in the chart are calculated using the measure:

      Incidences = CALCULATE(

      DIVIDE(SUM('ESTRAZIONE DATI'[Revenues (USD)]);SUM('ESTRAZIONE DATI'[Revenues (USD)]);0)

      -DIVIDE(SUM('ESTRAZIONE DATI'[Service]);SUM('ESTRAZIONE DATI'[Revenues (USD)]);0)

      -DIVIDE(SUM('ESTRAZIONE DATI'[Labor Cost]);SUM('ESTRAZIONE DATI'[Revenues (USD)]);0)

      -DIVIDE(SUM('ESTRAZIONE DATI'[Rental]);SUM('ESTRAZIONE DATI'[Revenues (USD)]);0)

      -DIVIDE(SUM('ESTRAZIONE DATI'[VDC]);SUM('ESTRAZIONE DATI'[Revenues (USD)]);0)

      -DIVIDE(SUM('ESTRAZIONE DATI'[FDC]);SUM('ESTRAZIONE DATI'[Revenues (USD)]);0))

       

      The sample database is this one:

       

      The result I want to obtain is to see the incidences sorted like in the first chart, with the years on the column and the type on the rows.

       

      Thank you all,

      Alessandro

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        10 years ago

        Aletom7

         

        I’m still confused about the output result. You want the chart to be sorted by category and year as below. Are the values in this chart incorrect now? If yes, could you please tell us how you want to calculate them with the original values in your sample database?

         

         

        Best Regards,
        Herbert