Forum Discussion

Lidou_Cand's avatar
Lidou_Cand
Helper III
5 years ago
Solved

Calculate Ratio Issue

Hello,
I'm newbie in dax.
I need your help to calculate a ratio.
I have a matrix like this: 
I need to calculate a ratio like this: Sales Customer / Sales Category

Can you help me ?

Is thera a way to add a pbix file to this discussion ?

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Lidou_Cand 

     

    You may try the following measure to see if it helps. The pbix file is attached in the end.

    Result = 
IF(
    NOT(ISBLANK([Sales Amount])),
    DIVIDE(
        [Sales Amount],
        CALCULATE(
            [Sales Amount],
            FILTER(
                ALL(Sales),
                RELATED('Product'[Category])=SELECTEDVALUE('Product'[Category])&&
                RELATED('Date'[Year]) in DISTINCT('Date'[Year])
            )
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Lidou_Cand ,  The information you have provided is not making the problem clear to me. Can you please explain with an example.

    In case you are looking for % of subtotal

    https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

    Percent of Tota;

    https://community.powerbi.com/t5/Desktop/Create-measure-of-total/td-p/7808

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. You can load on onedrive or dropbox and share a link
    Appreciate your Kudos.


      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Lidou_Cand , Checked file

        Try This

        % = divide([Sales Amount], CALCULATE( [Sales Amount], ALLSELECTED(Sales)))

         

        or

        % = divide([Sales Amount], CALCULATE( [Sales Amount], ALL(Sales)))

         

    • Lidou_Cand's avatar
      Lidou_Cand
      Helper III
      5 years ago

      Hello,

       

      Thank you for your help.

      It doesn't work because the denominator is still filtered by the row.

      Is there a way to remove all the filters for rows for the denominator ?

      Cause It may be confusing, I share a light pbix to explain what I need.

       

      Thks a lot

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Lidou_Cand 

     

    You may try the following measure to see if it helps. The pbix file is attached in the end.

    Result = 
IF(
    NOT(ISBLANK([Sales Amount])),
    DIVIDE(
        [Sales Amount],
        CALCULATE(
            [Sales Amount],
            FILTER(
                ALL(Sales),
                RELATED('Product'[Category])=SELECTEDVALUE('Product'[Category])&&
                RELATED('Date'[Year]) in DISTINCT('Date'[Year])
            )
        )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.