Dax to divide 2 columns based on fixed values

[Harshchauhan]

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 · Accepted solution]

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]

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 

[Harshchauhan]

This works wonders,

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