Forum Discussion

Adi98's avatar
Adi98
Frequent Visitor
4 years ago

Unable to create measure based on slicer

      

 

I have created this visual which shows storage by week of year and a chiclet slicer to select weeks. Now in the visual there is a text box which shows percentage increase or decrease of storage of the latest two weeks selected in slicer. As of now I am calculating that percentage difference manually and updating in textbox as required.

The formula for calculating percentage is = (storage of latest week - storage of next latest week)/ storage of next latest week * 100

for eg. (storage of week 13 - storage of week 12) / storage of week 12 * 100

 

My issue is that I want to create a measure that calculates this percentage value automatically as I select values in that chiclet slicer. Is there any way possible?

 

 

 

Custom Visuals
Data Modeling

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Adi98 , You have to create a measure like

     

    new measure =
    var _max = maxx(allselected(Date),Date[Week])
    var _min = minx(allselected(Date),Date[Week])
    return
    calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_max)) - calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_min))

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    Hi Adi98 ,

    If you a week number as a number, this will be a lot easier. You can do something like below to calculate for the value from the prior week.

    Value PrevWeek = 
VAR __PrevWeek =
    SELECTEDVALUE ( 'Table'[Week Number] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Week Number] = __PrevWeek )
    )

     

    Please see this pbix for a more detailed solution.  https://drive.google.com/file/d/1CQDOW3GrMh0AASAZTFGzXIQtuBoiBZxt/view?usp=sharing