Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
devenchj
Helper I
Helper I

Share % Calculation not working while setting up "Sort By Column" feature

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_IDSERIES_NAMESORT_ORDER
101Camry2
102Prius3
103Accord5
104Altima6
105Mirai4
106All Models1

 

Fact

SERIES_IDMonthAMT
101202001150
102202001300
103202001200
104202001250
105202001200
1062020011100
101202002200
102202002250
103202002400
104202002450
105202002100
1062020021400

 

DAX Calculated Measures Used : 

Sales Amount = SUM('Fact'[AMT])
Total Sales Amount = CALCULATE([Sales Amount],SeriesDim[SERIES_NAME] = "All Models")
Share % = DIVIDE([Sales Amount],[Total Sales Amount])*100
 
Screenshots URL : 
 
Thanks in advance !
 
1 ACCEPTED SOLUTION

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
 

View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

Hi @devenchj ,

 

Is this what you want?

n3.PNG

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])

n4.PNG

 

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.

Share % ResultShare % 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

Total Sales Amount = CALCULATE([Sales Amount],filter(all(SeriesDim),SeriesDim[SERIES_NAME] = "All Models"))
Find the attached file
 

Thanks @amitchandak , Using ALL function calculation worked as expected.

vivran22
Community Champion
Community Champion

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:

 

Capture.PNG

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.