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:   ...
Need Help
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

    • Aletom7's avatar
      Aletom7
      Regular Visitor
      10 years ago

      If it is possible, I want to have all the ratios calculated for category in a single column so that I can see wich is the incidence of every cost category on the total of the revenues.

      For example, if I use numerical data I obtain this result:

      I guessed to obtain what I want simply using the function of rapid calculation choosing the percentage of total, but what I obtain is this chart:

      So, this last chart is exactly what I want to see at the end, but with the right ratios.

       

      Thank you,

      Alessandro

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

        Aletom7

         

        Thanks for your description, I seem to get the point. I think we need to do some transformation for the original dataset with following steps.

         

        1. In Query Editor, remove two columns of “Type” and “COSTS”.
        2. Unpivot all the columns except the first column of “YEAR”.
        3. Click ‘Group By’ and set as below.
        4. Create a measure with following formula.
          Ratio = 
SUM ( 'ESTRAZIONE DATI'[NewValue] )
    / CALCULATE (
        SUM ( 'ESTRAZIONE DATI'[NewValue] ),
        'ESTRAZIONE DATI'[Attribute] = "REVENUES"
)


          Best Regards,
          Herbert