Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
Having issue with Share % measure while setting up "Sort By Column" feature, which seems bug.
without enabling Sort by column in SeriesDim table, Share % works correctly. but I need to show Series Name sorting as per another column SORT_ORDER in Slicer.
When I enable Sort by column in SeriesDim table, keeping Series Name and then Sort By Column --> SORT_ORDER . I can not see values displayed for all the series except Series "All Model" (which is being used in filter condition for calculation of "Total Sales Amount". (Share % reset for each Month)
Tables : SeriesDim and Fact (Join on SeriesID)
SeriesDim
SERIES_ID | SERIES_NAME | SORT_ORDER |
101 | Camry | 2 |
102 | Prius | 3 |
103 | Accord | 5 |
104 | Altima | 6 |
105 | Mirai | 4 |
106 | All Models | 1 |
Fact
SERIES_ID | Month | AMT |
101 | 202001 | 150 |
102 | 202001 | 300 |
103 | 202001 | 200 |
104 | 202001 | 250 |
105 | 202001 | 200 |
106 | 202001 | 1100 |
101 | 202002 | 200 |
102 | 202002 | 250 |
103 | 202002 | 400 |
104 | 202002 | 450 |
105 | 202002 | 100 |
106 | 202002 | 1400 |
DAX Calculated Measures Used :
Solved! Go to Solution.
I think, all need to be used and as long as that is part of seperate dimension it should work
Hi @devenchj ,
Is this what you want?
First, sort [SERIES_NAME] by [SORT_ORDER];
Second, create measures:
Sales Amount = SUM('Fact'[AMT])
Total sales amount =
CALCULATE(
SUM('Fact'[AMT]),
ALLEXCEPT(
SeriesDim,
SeriesDim[SERIES_NAME]
),
ALL('Fact'[Month], 'Fact'[AMT])
)
Share % = DIVIDE([Sales Amount],[Total Sales Amount])
I don't quite understand calculation logic of [Total sales amount], if this is not what you want, please tell me what [Total sales amount] returns.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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
Hello @devenchj,
I have used the following measures:
Sales Amount = SUM('Fact'[AMT])
OverAll Sales = CALCULATE([Sales Amount],ALL('Fact'))
% Sales = DIVIDE([Sales Amount], [OverAll Sales])
and getting the following results:
Is this what you are looking for?
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |