Forum Discussion
Value not returning all selected in DAX formula
Super UserHi,
Share some data and show the exact result you are expecting.
Policy Number Year Gross Written Prior Year Gross Written 1 2017 5 0 1 2020 10 5 1 2021 15 10 1 2018 20 15 1 2019 25 20 Total 75 50 For simplicity, here is a table I created to show policy number, gross written and prior year gross. What I would like is 3 potential results in the total row:
Scenario 1: User selects no years from the slicer - we would get a value exactly as we see above, where gross for all years is totalled and then prior year totalled
Scenario 2: User select single year. For example, if the user selects 2018, then gross written column would just show the total for policies from 2018, in this case it would be 20 and then for prior year it would show the total for 2017 which would be 5.
Scenario 3: User selects multiple year. For example, they select 2020 and 2019, then we would see the prior year total of 2019 and 2018. So in this case gross would be 35 and prior year would be 45
Is that helpful?
- Ashish_Mathur
Hi,
You have shared the matrix (result) table. Share a sample of the raw data in a format that can be pasted in an MS Excel file.
mahenkj2Solution Sage
Hi teetoes
In your sample data here, the last column is not prior year gross, but prior policy gross, right? Is it a measure or a recorded information already in data?
If you select slicer in 2018, gross written would be 20 and prior year gross would be of 2017 i.e. 5, but your last column is showing prior policy gross of 15.
It may be like that, but first like to confirm with you.
Thanks.
Prior year gross is a measure of the sum of gross from the prior year's written premium. Policies have more than one row in our db so we roll all of these amounts into one in the original query to get a single value for gross written premium.
I see why the confusion. Policy number should all be 1 in this table to make the example work. Sorry about that.