1

I have a table with dataset, I need create visual with slicers over time, where i can select 1st item and 2nd item and calculated delta between them.

I’ve done that by create 2 tables with Dax

test_item_1 = values('tbl_data'[Item])
test_item_2 = values('tbl_data'[Item])

Add 2 slicers with list of items for 1 and 2.

Then in table - tbl_data, create new measure to calculate delta between selected items in slicers over time

test_delta = calculate(sum('tbl_data'[Value]),'tbl_data'[Item] = SELECTEDVALUE(test_item_1[Item 1])) - calculate(sum('tbl_data'[Value]),'tbl_data'[Item] = SELECTEDVALUE(test_item_2[Item 2]))

Could you please advise how to create Average based on calculated delta for all selected datetime period.

Chart supposed to be like that as example.

enter image description here

Date Item Value
2023-04-27 item_1 20
2023-04-26 item_1 30
2023-04-25 item_1 40
2023-04-24 item_1 50
2023-04-27 item_2 100
2023-04-26 item_2 110
2023-04-25 item_2 120
2023-04-24 item_2 130
2023-04-27 item_3 150
2023-04-26 item_3 160
2023-04-25 item_3 170
2023-04-24 item_3 180

I'm able to get and calculate delta but don't know how to calculate average for that delta as a single line.

Roma P
  • 21
  • 3

1 Answers1

2

Try the following measure:

=
CALCULATE (
    DIVIDE (
        [test_delta],
        DISTINCTCOUNT ( tbl_data[Date] )
    ),
    ALL ( tbl_data[Date] )
)
Jos Woolley
  • 8,564
  • 2
  • 4
  • 9