Conditionnal Divide of Sums

[Xavier]

Hello,

I have a table like this 

Period | Site | Client | Revenue | Expense

202204 | France | X | 1000 | 75

202205 | France | X | 1000 | 100

202205 | UK | Y | 500 | 100

202204 | UK | Z | 0 | 50

I'd like to create a measure (ratio in %) that I would explain like this in SQL :

Select SUM(Expense)/SUM(Revenue)

Where Revenue > 0

Group By Period, Client, Site

The "Group By" describes the fact this measure should automatically be calculated when I select my filter Period and/or Client and/or Site

Any Help would be greatful !

Thx

[v-zhangti · Accepted solution]

Hi, @Xavier 

You can try the following methods.

Measure =
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Expense] ), SUM ( 'Table'[Revenue] ) ),
    FILTER (
        ALL ( 'Table' ),
        [Revenue] > 0
            && [Period] = SELECTEDVALUE ( 'Table'[Period] )
            && [Site] = SELECTEDVALUE ( 'Table'[Site] )
            && [Client] = SELECTEDVALUE ( 'Table'[Client] )
    )
)

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@Xavier , Try a measure like

calculate( divide(SUM(Table[Expense]),SUM(Table[Revenue])), filter(Table, Table[Revenue] >0 ))

Group will be what you take in visual

DAX vs SQL: https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj