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
    2 years ago

    This works wonders,

     

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

  • 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