Forum Discussion

mrfsca's avatar
mrfsca
Frequent Visitor
1 year ago

YTD Measure Using Slicer Selection

Hi, I am doing a simple formula in excel where i wanna calculate order qty and target (both YTD) to put in a column and line chart. When I'm transforming it into PBI, I made 2 quick measure using TOT...
Need Help
Tips and Tricks
Tutorial Requests
Irwan's avatar
Irwan
Super User
1 year ago

hello mrfsca 

 

please check if this accomodate your need.

 

i assumed you good with accumulated value. but just incase, here are the DAX for both accumulated value.

Order Accum = 
var _Type = SELECTEDVALUE('Table'[Type])
var _Group = SELECTEDVALUE('Table'[Group])
var _Index = SELECTEDVALUE('Table'[Index])
Return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Type]=_Type&&
        'Table'[Group]=_Group&&
        'Table'[Index]<=_Index
    ),
    'Table'[ order ]
)
TGT Accum = 
var _Type = SELECTEDVALUE('Table'[Type])
var _Group = SELECTEDVALUE('Table'[Group])
var _Index = SELECTEDVALUE('Table'[Index])
Return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Type]=_Type&&
        'Table'[Group]=_Group&&
        'Table'[Index]<=_Index
    ),
    'Table'[Target']
)

 

Here is the DAX for percent Order YTD.

Order YTD = 
DIVIDE(
    [Order Accum],
    MAXX(
        ALLSELECTED('Table'),
        [Order Accum]
    )
)
for TGT YTD, the DAX is exactly same but different expression (use "target" instead of "Order")
TGT YTD = 
DIVIDE(
    [TGT Accum],
    MAXX(
        ALLSELECTED('Table'),
        [TGT Accum]
    )
)

 

also, dont mind the index column, i used it for ordering but i assumed you have another column for indexing since you have done the accumulated value calculation.

 

Hope this will help.

Thank you.