Forum Discussion

fortuit's avatar
fortuit
Frequent Visitor
7 years ago

Ratio between two columns in Matrix Visual

Dear community,

I would like to create a new column (YtD Budget/Budget) in a table which is the result of the division of two columns in a matrix visual (see below ). I would like no having one single value but one per category (here: per row).

Does anyone know how to do it ? This is of course something really easy to perform in excel but less with DAX...

Thanks for the feedback.

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    fortuit,

    Without seeingthe data model, you can create a measure like below: 

    YTD Budget/Budget =
    DIVIDE ( SUM ( 'Table Name'[YTD Budget] ), SUM ( 'Table Name'[Budget] ), 0 )

     

    • fortuit's avatar
      fortuit
      Frequent Visitor
      7 years ago

      @ NickNg278 Thank you for the feedback.

      However such a measure won't work in my case because YTD budget and Budget are coming from the same colum in my data base : FinMetric (see below). The target is to have a measure of the YTD perf : YTD Budg/Budget per country (here US and Turkey). I tried to do it this way via three measures :

       
      YTD Budget = CALCULATE(SUM(FinalDB[FinMetric]);FILTER(FinalDB;FinalDB[FIN_Global1]="YtD Budget"&&FinalDB[FinMetric]>0))
       
      Budget = CALCULATE(SUM(FinalDB[FinMetric]);FILTER(FinalDB;FinalDB[FIN_Global1]="Budget"))
       
      YTD Perf = YTD Budget/Budget
       
      But the final result is such that I only get one measure YTD Perf (on the whole sample) while I would like to have one per country...