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 poole...
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]
)
jbaisley's avatar
jbaisley
Helper I
3 years ago

No worries 🙂 I could have been clearer. That function is returning a repeating zero.0'

tamerj1's avatar
tamerj1
Community Champion
3 years ago

jbaisley 

Are you using the dmsame data as pet the example? If so you shouldn't get 0. Anyway, please increase the number of decimal points just yo confirm you're actually getting zero not a number rounded to zero. 
do you zero or blank values in the ratio column?
also advise if you gave a unique date or index column. 

  • jbaisley's avatar
    jbaisley
    Helper I
    3 years ago

    Aww! Its working but! The filters are not impacting the function (ofcourse) So the solution works! Thanks tamerj1 

  • jbaisley's avatar
    jbaisley
    Helper I
    3 years ago

    Yep, same data (there is more of it than in the exmaple), and expanding the decimal to 15 places. Its a repating 0. Becasue it an odds ratio I moved it back a step to the percent, as the fucntion would be (1/ Odds[percentage)-1 = Odds[Ratio]. Doing that I get 'infinity'

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      OK, am I crazy to point out that multiplying the inverse of the ratio does not produce the same result as dividing the numbers? See PBIX attached. PRODUCX('Table', 1/[Ratio]) with the sample data returns 1.75 while dividing the numbers returns 3.24. Where is my math failing me?

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi Greg_Deckler 
        You are 100% correct but it is just about the first value which goes the numerator, this is what I was asking for a unique date or index column to calculate the first value visible in the current filter context and divide it by the result of the PRODUCTX after filtering out the very same value from the table. Not sure if jbaisley has double checked the results not even sure if this is what he wants or in fact he just wants to have the result of the PRODUCTX right away.
        I don't know what is this calculation about but to me it makes more sense to say that divide the ratios by each other means 1/R1/R2/R3/......etc