topic Re: Dax to divide 2 columns based on fixed values in DAX Commands and Tips

Dax to divide 2 columns based on fixed values

Harshchauhan — Tue, 29 Oct 2024 12:13:48 GMT

Hello Enthusiasts

I have a below input table

Bucket TOS Bucket LM TOS LM

B1 1 B0 3

B1 2 B0 4

B1 3 B1 5

B2 4 B1 6

B2 5 B2 7

B3 6 B3 8

I need the below Output table

Bucket %

B1. 0.85 ( sum of TOS for bucket B1(1+2+3=6)/sum of TOS LM FOR bucket lm B0(3+4=7)

B2. 0.81 ( sum of TOS for bucket B2(4+5=9)/sum of TOS LM FOR bucket lm B1(5+6=7)

B3. 0.86 ( sum of TOS for bucket B3(6)/sum of TOS LM FOR bucket lm B2(3+4=7)

Thanks in advance

Re: Dax to divide 2 columns based on fixed values

SamWiseOwl — Tue, 29 Oct 2024 11:09:13 GMT

Hi Harshchauhan

The pattren appears to be Bucket Total divided by Bucket -1 Total.

TOS / LM TOS =

var buck = SELECTEDVALUE('Table'[Bucket])

var lmbuck = Left(buck,1) & (RIGHT(buck,1) - 1)

RETURN

CALCULATE(

sum('Table'[TOS]), all('Table'), 'Table'[Bucket] = buck)

CALCULATE(

sum('Table'[LM TOS]), all('Table'), 'Table'[Bucket LM] = lmbuck)

If you meant to divide the Bucket total by the Bucket LM total that would instead be:

TOS / LM TOS =

var buck = SELECTEDVALUE('Table'[Bucket])

var lmbuck = SELECTEDVALUE('Table'[Bucket LM])

RETURN

CALCULATE(

sum('Table'[TOS]), all('Table'), 'Table'[Bucket] = buck)

CALCULATE(

sum('Table'[LM TOS]), all('Table'), 'Table'[Bucket LM] = lmbuck)

Re: Dax to divide 2 columns based on fixed values

Harshchauhan — Tue, 29 Oct 2024 13:55:08 GMT

This works wonders,

Thanks alot Sam for your attention to my query and prompt solution

Re: Dax to divide 2 columns based on fixed values

Harshchauhan — Sat, 16 Nov 2024 05:38:29 GMT

Hi SamWiseOwl

I have another requirement based on this query

Now I am having only 3 columns

Month bucket TOS

Oct. B1. 7

Oct. B2. 8

Oct. B3. 9

Nov. B1. 4

Nov. B2. 5

Nov. B3. 6

Dec. B1. 7

Dec. B2. 8

Dec. B3. 9

I want output as sum of tos for this month this bucket/ sum of tos for last month last bucket

For example Sum of tos for B2 Nov/ Sum of TOS for B1 Oct = 5/7=0.71

Can you please try a dax measure for the above problem

Let if know if you need more explanation

Thanks in advance