Forum Discussion

Harshchauhan's avatar
Harshchauhan
Frequent Visitor
2 years 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 ...
"DAX help"
SUMMARIZE
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years 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)
SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
2 years 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's avatar
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