Calculate Ratio Issue

[Lidou_Cand]

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 ?

[v-alq-msft · Accepted solution]

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]

@Lidou_Cand 

please try to create a measure

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

[Lidou_Cand]

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

[amitchandak]

@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]

PBIX Ratio Issue 

Hello,

This a pbix I used for my test.

Thanks for your help.

[amitchandak]

@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]

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