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 ther...
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.

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.


Lidou_Cand's avatar
Lidou_Cand
Helper III
5 years ago

PBIX Ratio Issue 

 

Hello,

This a pbix I used for my test.

Thanks for your help.

 

  • 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,

       

      When I use ALL or ALLSELECTED for the whole Table (Sales), I have cross join with row with no data.

      Is there a way to calculate the measure for only rows with data ?

      In SQL it will be: CASE WHEN [Measure] is not null then Calculate......

      Is a way to do it in DAX ??