Creating a measure dynamically based on a slicer value

Question

I have a set of sales data and this data is summarised by account , year and volume

I can create a measure to calculate my 22 sales volume "22 Sales Volume"

I then want to have an additional measure showing the sales volume for another year based on the slicer value the user selects - "Selected Year Volume"

I would then like to create another measure which calculates the difference between 22 and the value for the selected year giving a gap analysis between the sales for 22 and the total for the selected year - "22 Sales Volume" - "Selected Year Volume"

For example if i select year 20 then it would look like below

i have tried using the hasonefilter command but cannot get it to sum the qty for that year basius the selcted value


account	year	sales		22	selected year 20	gap
a	22	100	a	100	300	-200
a	21	200	b	0	900	-900
a	20	300
a	19	500
a	18	600
b	22	0
b	21	500
b	20	900
b	19	500
b	18	600

anonymous · Accepted Answer

Hi&nbsp;MNGoodyear&nbsp;,
&nbsp;
You need a seperate year table for the sales of the selected year. Here's the solution.
Raw data is as follows which you provided.

1.Create a table using dax. There's no relationship between tables.

2.Create a measure for the sales of the selected year from the year table.
selected year =
CALCULATE (
    SUM ( 'Table'[sales] ),
    FILTER (
        ALL ( 'Table' ),
        [account] = MAX ( 'Table'[account] )
            &amp;&amp; [year] = SELECTEDVALUE ( 'Year'[year] )
    )
)

And the gap measure is below.
gap = SUM('Table'[sales])-[selected year]
3.I made arrows indicating the source of the slicer field and which value was generated.&nbsp;

You can download my attachment for more details.
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Creating a measure dynamically based on a slicer value

1 Reply

Related content

Need help creating a measure

Help to create DAX Measure

Dynamic measure filtering

Create a dynamic measures using field parameter measure values

Creating a dynamic slicer