Forum Discussion

rampsaladi's avatar
rampsaladi
Frequent Visitor
8 years ago
Solved

How to Divide multiple rows for with another row from the same Column

Hi   I'm trying to create a measure where the denominator will always be same but the numerator will change. Both the Numerator and Denominator are referencing the same column. Below is an example....
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    rampsaladi

     

    Seems your logic is what parry2k has stated.

     

    As a calculated column, try this

     

    Column =
DIVIDE (
    TableName[$],
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)

    As a MEASURE

     

    MEASURE =
DIVIDE (
    SUM ( TableName[$] ),
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)
parry2k's avatar
parry2k
Super User
8 years ago

what is logic which decides the value denominator? Seems like it is $ value of 1st row of each customer where product = "Prod1" or something else?

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

rampsaladi

 

Seems your logic is what parry2k has stated.

 

As a calculated column, try this

 

Column =
DIVIDE (
    TableName[$],
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)

As a MEASURE

 

MEASURE =
DIVIDE (
    SUM ( TableName[$] ),
    CALCULATE (
        SUM ( TableName[$] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Customer] ),
            TableName[Product] = "prod 1"
        )
    )
)
  • rampsaladi's avatar
    rampsaladi
    Frequent Visitor
    8 years ago

    This is great It worked wonders  thanks