Forum Discussion

mmvohra's avatar
mmvohra
Icon for Helper II rankHelper II
6 months ago
Solved

Need help regarding fine measure formulation

I want to make measure as per the following logic: Filter table → only rows where [Dif KM_1] <= ParameterValue. Within each Consolidated ID: Find minimum date. Find the immediate date after the m...
Need Help
Show and Tell
Irwan's avatar
Irwan
Icon for Super User rankSuper User
6 months ago

hello mmvohra 

 

the file in the link seems to be missing.

Thank you.

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    6 months ago

    hello mmvohra 

     

    you can do this in various ways, but here i show how i usually do.

    you can do this in either calculated column or measure.

    1. using calculated column

    - create a new table to summarize those ID less than 3000.

    Summarize =
    SUMMARIZE(
        FILTER(
            'Work',
            'Work'[Dif KM_1]=0||'Work'[Dif KM_1]<=2000
        ),
        'Work'[ID],
        'Work'[ConsolidatedID],
        'Work'[Distance (km)],
        'Work'[Date],
        'Work'[Price],
        'Work'[Person Type],
        'Work'[Name],
        'Work'[Link]
    )
    - create a new calculated column with following DAX for price different.

     

    Price Different =
    var _Previous = 
    MAXX(
        FILTER(
            'Summarize',
            'Summarize'[ConsolidatedID]=EARLIER('Summarize'[ConsolidatedID])&&
            'Summarize'[Date]<EARLIER('Summarize'[Date])
        ),
        'Summarize'[Price]
    )
    Return
    IF(
        not ISBLANK(_Previous),
        'Summarize'[Price]-_Previous
    )
     - create a new measure for sum value of price different.
    Sum Column = SUM('Summarize'[Price Different])

     

    2. using measure

    - create a new measure with following DAX for calculating price different.

    Price Different =
    var _Previous = 
    MAXX(
        FILTER(
            ALL('Work'),
            'Work'[ConsolidatedID]=SELECTEDVALUE('Work'[ConsolidatedID])&&
            'Work'[Date]<SELECTEDVALUE('Work'[Date])&&
            'Work'[Dif KM_1]<=2000
        ),
        'Work'[Price]
    )
    Return
    IF(
        not ISBLANK(_Previous),
        SELECTEDVALUE('Work'[Price])-_Previous
    )
    - create a new measure with following DAX for sum value.
    Sum Measure =
    SUMX(
        FILTER(
            ALL('Work'),
            'Work'[ConsolidatedID]=SELECTEDVALUE('Work'[ConsolidatedID])&&
            'Work'[Dif KM_1]<=3000
        ),
        [Price Different]
    )
    - create a slicer for consolidateID filter and KPI visual for showing sum value (either card or KPI or any visual you want)

     

    Hope this will help.

    Thank you.

    • mmvohra's avatar
      mmvohra
      Icon for Helper II rankHelper II
      6 months ago

      I want total sum but it should be dynamic based on the value of parameter. 
      Calculated columns and table are of no use as parameter does not work on them.
      I want a measure that can dynamically filter the table based on dif KM_1<=parameter value condition and then find the sum of difference in price meeting the criteria.