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