Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Divide rows

Hello,   I am trying to create a table in which I want to include the rent, the square feet and calculate the Rent/square foot. In the subtotal I get wrong number because the default action in Powe...
Need Help
  • Datatouille's avatar
    Datatouille
    8 years ago

    Hi

     

    I guess you computed your Rent/Square Feet as a calculated column using the DAX language.

    Because this is a ratio, you'd better use a measure rather than a calculated column. See more explanation here: https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

    Ralph Kimball summarizes this concept with this sentence : "You should always to the ratios of the sum rather than the sum of the ratios"

     

    So, in your example, go to 'Modelling' Tab, create the following measure:

    Rent/Squarefeet = Divide ( Sum(YourTable[Rent]) , Sum(YourTable[Square Feet]) , 0)

     

    And use this brand new measure in the "values" of your tables & matrix.

     

     

Datatouille's avatar
Datatouille
Solution Sage
8 years ago

Hi

 

I guess you computed your Rent/Square Feet as a calculated column using the DAX language.

Because this is a ratio, you'd better use a measure rather than a calculated column. See more explanation here: https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Ralph Kimball summarizes this concept with this sentence : "You should always to the ratios of the sum rather than the sum of the ratios"

 

So, in your example, go to 'Modelling' Tab, create the following measure:

Rent/Squarefeet = Divide ( Sum(YourTable[Rent]) , Sum(YourTable[Square Feet]) , 0)

 

And use this brand new measure in the "values" of your tables & matrix.

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Many thanks Excelside, this looks to work fine.