Hi v-jiascu-msft
Great job, it works!.
1)My first problem now is how to create the index in my real model.
Within my Dummy model, I simplified the names, using "Family 1, Family 2" and so on so forth, but the real model, every family has different caracters length, so we cannot use this approach.
2)The second problem is, this solution works if you arrange data by Family, but you tend to arrange by "Total Sales" (descending), in such case, this doesn't work.
As you could see, Master_Item_All presents unique list of Item_Number, but not unique list of Families, so automatic Index is not gonna work here.
My attempt:
1)What I have done is the following:
- In Family Table, add a calculated column "Total Sales YTD" (important because I have a slicer on the report filtering by 2017)
- Funny thing= I cannot create the measure to filter total sales by current year without writing specifically 2017 or 2018......support??:smileysurprised:
- In Family Table, add a RANKX function to rank all families based in total sales 2017 (or total sales current year...)
- Optional: In Master_Item_All, I've created a calculated column with Related, bringing the Rank Value from Family Table.
- In Key Measures, I create a measure using similar sintax (Calculate + Filter) to create comulative sales.
- You can use "Family" as filter table or "Master_item_All" whether you used "Related" step before.
2) If you want to have cumalitive sales based on Names (alphabetical)
- Arrange by Family Name on Family Table (ascending or descending, at will)
- Create automatic index number, starting in 1.
- Do the steps as mentioned above.
3)Any idea how to deal with this based on "Arrange by Name" or "Arrange by Total Sales" simultaneously? something like IF( _____).
I've uploaded the file for your perusal, awaiting your comments.
https://www.dropbox.com/s/p7kyhp8vt972axl/Dummy%20Model.pbix?dl=0
What do you think?:smileysad:
Advocate III
