Forum Discussion
Share % Calculation not working while setting up "Sort By Column" feature
I think, all need to be used and as long as that is part of seperate dimension it should work
Total Sales Amount = CALCULATE([Sales Amount],filter(all(SeriesDim),SeriesDim[SERIES_NAME] = "All Models"))Find the attached file
Hello v-lionel-msft ,
Logic for Total Sales Amount - There is series called "All Models" already available in dimension table. so fact table has record for "All Models" which is an aggreagated records for all the available series in dimesion. you can consider this records as Total. But Grainularity of Fact table has multiple dimension key and for each foriegn key combination there is aggregated records with "All Series". So if I do SUM on series column it will be doubled up.
so internally , Amount of all series except "All Models" = "All Models"
To add more complex scenario - actual data model has around 8-10 dimension table, some of dimension columns used in slicer (Location, Model Year, Manufacturer etc) , some of them used in Table and Matrix visuals (Region, Series, Business Month) , some of them used in Visual level and Page Level filter (Vehicle Type, Vehicle Segment)
So my Share % measure should be slice and dice as per columns placed in visuals and as per other selection from Slicer. so unfortunately I can't use any fixed combination of columns or use of DAX functon ALLEXCEPT or ALLSELECTED . (otherwise I have to create measure for each column combination displaying in different visuals and in different reports.
Here is expected result : which is working without using Sort by Column option. But as soon as I enable this feature calculation is breaking. which should not be the case, sorting should not affect Measure calculation.
Share % Result
PBIX also shared here:
https://drive.google.com/open?id=12U3EF7xjwj8DMjGZPq5b9XpVBz2z6UXC
please let me know if more explanation needed.
-Devendra
I think, all need to be used and as long as that is part of seperate dimension it should work
Thanks amitchandak , Using ALL function calculation worked as expected.