Dax Measure dividing the sum of one column by the sum of another

[SiGill1979]

Hi

Hope someone can help. I've got the following Measure

GM% = if(DIVIDE(SUM(ProfitabilityMASTERFinal[GM]),SUM(ProfitabilityMASTERFinal[Net Sales]))>9.9999,9.9999,IF(DIVIDE(SUM(ProfitabilityMASTERFinal[GM]),SUM(ProfitabilityMASTERFinal[Net Sales]))<-9.9999,-9.9999,DIVIDE(SUM(ProfitabilityMASTERFinal[GM]),SUM(ProfitabilityMASTERFinal[Net Sales]))))

If I then add this measure to a table and and the Year column in, I get the same result for all years. How do I get it to give me the correct result for each year. Is there a Partition By or Over function in Power BI?

[v-yangliu-msft · Accepted solution]

Hi  @SiGill1979 ,

Do you have any other fields on your form besides [Year] because sum() will group based on the fields placed on the form Visual.

If you want to group based on the [Year] field, you can place only [Year] and measure in the Form Visual.

If you have the rest of the fields and the [Year] field in Form Visual, and you want to group by [Year] field, you can change the measure。

Measure 2 =
var _sumGM=
SUMX(FILTER(ALL('ProfitabilityMASTERFinal'),'ProfitabilityMASTERFinal'[Year]=MAX('ProfitabilityMASTERFinal'[Year])),[GM])
var _sumnet=
SUMX(FILTER(ALL('ProfitabilityMASTERFinal'),'ProfitabilityMASTERFinal'[Year]=MAX('ProfitabilityMASTERFinal'[Year])),[Net Sales])
RETURN
 if(
    DIVIDE(_sumGM,_sumnet)>9.9999,9.9999,IF(DIVIDE(_sumGM,_sumnet)<-9.9999,-9.9999,DIVIDE(_sumGM,_sumnet)))

Best Regards,

Liu Yang

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

[lbendlin]

That would be a question for your data model wiring.  Same value in all rows = can't get there from here (for example you are trying to control a dimension from a fact).  Which columns did you use for the visual?

By the way your measure can be simplified either via variables or via MinMax:

GM% =
MAX (
    MIN (
        DIVIDE (
            SUM ( ProfitabilityMASTERFinal[GM] ),
            SUM ( ProfitabilityMASTERFinal[Net Sales] ),
            0
        ),
        9.9999
    ),
    -9.9999
)