Problem with Ratios

[Aletom7]

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

[v-haibl-msft]

@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]

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]

@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]

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]

@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
   

[Aletom7]

The solution is good, but for me is not useful. Because I want to see these percentages not on the total of costs and revenues, but on the these totals belonging to particular CDC (center of costs, localities).

Is it possible to add this feature?

Thank you,

Alessandro

[Baskar]

Hi Aletom,

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

[Aletom7]

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

[Baskar]

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

[Aletom7]

No, it does not work. 😞

This is what I obtain: