Forum Discussion

GouldM's avatar
GouldM
Regular Visitor
1 year ago
Solved

Comparing to a 'Previous Category'

Supposing I have data in this table

Category   Value

1                3

2                6

3                7

 

How could I create a measure that DIVIDEs the value of a category but the value of the Previous category

Result would in the above be

Category Value Comparator

1               3       Blank()

2               6       2   (6/3)

3               7       2.16667

 

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    GouldM , Create a measure using

    DAX
    Comparator =
    VAR CurrentCategory = 'Table'[Category]
    VAR PreviousCategoryValue =
    CALCULATE(
    MAX('Table'[Value]),
    FILTER(
    'Table',
    'Table'[Category] = CurrentCategory - 1
    )
    )
    RETURN
    IF(
    ISBLANK(PreviousCategoryValue),
    BLANK(),
    DIVIDE('Table'[Value], PreviousCategoryValue)
    )

  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    GouldM 

    you can try this to create a column

     

    Column =
    var _last=maxx(FILTER('Table','Table'[category]=EARLIER('Table'[category])-1),'Table'[value])
    return if(ISBLANK(_last),blank(),DIVIDE('Table'[value],_last))
    or try this to create a measure
     
    Measure =
    var _last=maxx(FILTER(all('Table'),'Table'[category]=max('Table'[category])-1),'Table'[value])
    return if(ISBLANK(_last),blank(),DIVIDE(max('Table'[value]),_last))
    pls see the attachment below

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    GouldM , Create a measure using

    DAX
    Comparator =
    VAR CurrentCategory = 'Table'[Category]
    VAR PreviousCategoryValue =
    CALCULATE(
    MAX('Table'[Value]),
    FILTER(
    'Table',
    'Table'[Category] = CurrentCategory - 1
    )
    )
    RETURN
    IF(
    ISBLANK(PreviousCategoryValue),
    BLANK(),
    DIVIDE('Table'[Value], PreviousCategoryValue)
    )

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    You could create a measure like

    Comparator =
VAR CurrentValue = SUM( 'Table'[Value] )
VAR PrevValue = CALCULATE(
	SUM( 'Table'[Value] ),
	OFFSET(
		-1,
		ALLSELECTED( 'Table'[Category] ),
		ORDERBY( 'Table'[Category], ASC )
	),
	ALLEXCEPT( 'Table', 'Table'[Category] )
)
VAR Result = DIVIDE( CurrentValue, PrevValue )
RETURN Result
  • v-aatheeque's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi GouldM 

    If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

     

    If you have any further questions, feel free to reach out.
    Thank you for being a valued member of the Microsoft Fabric Community Forum!

     

    • GouldM's avatar
      GouldM
      Regular Visitor
      1 year ago

      Hi none of the answers addressed my problem.

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        1 year ago

        GouldM 

        you can try this to create a column

         

        Column =
        var _last=maxx(FILTER('Table','Table'[category]=EARLIER('Table'[category])-1),'Table'[value])
        return if(ISBLANK(_last),blank(),DIVIDE('Table'[value],_last))
        or try this to create a measure
         
        Measure =
        var _last=maxx(FILTER(all('Table'),'Table'[category]=max('Table'[category])-1),'Table'[value])
        return if(ISBLANK(_last),blank(),DIVIDE(max('Table'[value]),_last))
        pls see the attachment below