Forum Discussion

jbaisley's avatar
jbaisley
Helper I
3 years ago
Solved

Multiply Values from Single Column

Hello folks,

 

Hopfully, a simple one. I have column of odds by categories and location. The user can filter by category and location and I wan't to divide the remaining values together for a pooled odds ratio. How do you divide (or even mutiply) all vlaues in a single column togehter? In the below table the answer should be 3.47 = 1.36/3.23/2.64/0.41/0.12

 

LocationCategoryRatio
UKFatigue1.36
UKHead Ache3.23
UKSweating2.64
UKNausa 0.41
UKSwelling0.12
DAX
Power Query
  • tamerj1's avatar
    tamerj1
    3 years ago

    jbaisley 

    I thought [Ratio] is a measure 

    otherwise no need to summarize just

    =
PRODUCTX (
    'Table', 
    1 / 'Table'[Ratio]
)

17 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    jbaisley Hmm... DAX has a PRODUCTX function to multiply a column of numbers together but no equivalent DIVIDEX function that I am aware of. Will think on this. Also, I get 3.24 dividing all of those numbers.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi jbaisley 

    please try

    =
PRODUCTX (
    SELECTCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Location], 'Table'[Category] ),
        "@Ratio", [Ratio]
    ),
    1 / [@Ratio]
)
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      tamerj1 I went down that path. The syntax is broken, can't pick [Ratio] out of a summarized column that doesn't include it. Also, PRODUCTX('Table',1/[Ratio]) is equivalent and that returns 1.75.

    • jbaisley's avatar
      jbaisley
      Helper I
      3 years ago

      Thanks tamerj1 . IT doesn't like the `[Ratio]`. `Ratio` is a column, formatted as a decimal if that helps.

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        jbaisley 

        I thought [Ratio] is a measure 

        otherwise no need to summarize just

        =
PRODUCTX (
    'Table', 
    1 / 'Table'[Ratio]
)
  • jbaisley's avatar
    jbaisley
    Helper I
    3 years ago

    I have returned for advice on extending this function! Suppose I have a slicer for the symtopms in the first post. How can I filter the Odds[Ratio] to the selected values of Odds[Category]?