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
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
  • Aletom7's avatar
    Aletom7
    Regular Visitor
    10 years ago

    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