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