cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Super User IV
Super User IV

Re: 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
 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

5 REPLIES 5
vivran22 Solution Sage
Solution Sage

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

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

Microsoft v-lionel-msft
Microsoft

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

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.

 

 

devenchj Helper I
Helper I

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

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.

Result.pngShare % Result

 

PBIX also shared here:

https://drive.google.com/open?id=12U3EF7xjwj8DMjGZPq5b9XpVBz2z6UXC

 

please let me know if more explanation needed.

 

-Devendra

Super User IV
Super User IV

Re: 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
 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

devenchj Helper I
Helper I

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

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors