Forum Discussion

Frequent Visitor

1 year ago

Solved

Dax to divide 2 columns based on fixed values

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

SamWiseOwl
1 year ago
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)

3 Replies

SamWiseOwl
Super User
1 year ago
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)
- Harshchauhan
  Frequent Visitor
  1 year ago
  This works wonders,
  
  Thanks alot Sam for your attention to my query and prompt solution
- Harshchauhan
  Frequent Visitor
  1 year ago
  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