cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbaisley
Regular Visitor

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
1 ACCEPTED SOLUTION

@jbaisley 

I thought [Ratio] is a measure 

otherwise no need to summarize just

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

View solution in original post

17 REPLIES 17
jbaisley
Regular Visitor

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]?

@jbaisley 

The measure should be filtered with no issues 

tamerj1
Super User
Super User

Hi @jbaisley 

please try

=
PRODUCTX (
    SELECTCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Location], 'Table'[Category] ),
        "@Ratio", [Ratio]
    ),
    1 / [@Ratio]
)

Thanks @tamerj1 . IT doesn't like the `[Ratio]`. `Ratio` is a column, formatted as a decimal if that helps.Screenshot 2022-10-18 at 16.10.55.png

@jbaisley 

Do you have a unique date or index column?

@jbaisley 

I thought [Ratio] is a measure 

otherwise no need to summarize just

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

No worries 🙂 I could have been clearer. That function is returning a repeating zero.Screenshot 2022-10-19 at 07.45.01.png0'

@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. 

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

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'

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?

Greg_Deckler_0-1666179281745.pngGreg_Deckler_1-1666179327583.png

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@tamerj1 OK, I just wanted to make sure I wasn't going crazy. Does seem like there ought to be a PRODUCTX equivalent in DAX, DIVIDEX? Otherwise, to truly get the division of a column of numbers seems to stray into the recursive space and we all know where that leads...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks folks, 

 

The use is a dodgy pooled ratio calculation. In theory (I cannot stress that enough) you can divide indivudal probabilty ratios to get a joint odds ratio. There is lots of pre-work to ensure that the ratios are correct and you need to be super careful that the ratios are coming from the same popualtion size. Typically you would only attempt this with perfect knowledge of a popualtion, instead we are using prevelance rate statistics to make odds ratios. The client has asked to be able to see their corss impact on one another, hense pooling them (again should only be done in specific circumstances). As both of you have discovered the results are suspect, but we needed to build it to prove the point. So it does work as intended 🙂 

 

-Jacob

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler you are correct re: the 3.24 (long day). I will take a look at PRODUCTX.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors