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.

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

Lidou_Cand 

please try to create a measure

=SUM(AMOUNT)/CALCULATE(SUM(AMOUNT),ALLEXCEPT(TABLE,CATEGORY))

 

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