cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheGreatestGoat Frequent Visitor
Frequent Visitor

Percentage Of Total by Category Over Time

Hi All, 

I'm stuggling to come up with the right DAX to solve my probelm. I have a Shipping Fact Table and some example data is as follows:

dateorder_idshippingshipping_code
5/08/201874712711111116.99E2222
5/08/20187471481111117.99S1111
5/08/201874714411111116.99E2222
5/08/20187466631111110F1000
4/08/20187462971111110F1000
4/08/20187467171111117.99S1111
4/08/20187467811111110F1000
4/08/20187464861111110F1000


I would like to graph percentages of each shipping code relative to the total number of orders over time (days) - I have a time dimension table linked. 

For example the desired result would be for the table above:

05/08/2018:
Total Orders: 4

E2222 - 50% (2 orders)
S1111 - 25% (1 order)
F1000 - 25% (1 order)

I then want to creat a line graphs for each shipping code % Of Total Orders over time. 

The purpose of this is to look at on which days 'Express Post' makes up the greatest percentage of total order i.e. 7 days before Christmas, so that we can promote an offer at this time. 

Any help with this would be greatly appreciated!

Thanks! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Percentage Of Total by Category Over Time

Hi @TheGreatestGoat

calculate measures as below

Measure = CALCULATE(COUNT(Sheet6[shipping_code]),ALLEXCEPT(Sheet6,Sheet6[date]))

Measure 2 = CALCULATE(COUNT(Sheet6[shipping_code]),ALLEXCEPT(Sheet6,Sheet6[date],Sheet6[shipping_code]))

Measure 3 = [Measure 2]/[Measure]

12.png

 

14.png

15.png

Best Regards

Maggie

2 REPLIES 2
Super User
Super User

Re: Percentage Of Total by Category Over Time

Hi,

 

Try this measure

 

=COUNTROWS(Data)/CALCULATE(COUNTROWS(Data),ALL(Data[shipping_code]))

 

Hope this helps.

 

Untitled.png

 

Highlighted
Community Support Team
Community Support Team

Re: Percentage Of Total by Category Over Time

Hi @TheGreatestGoat

calculate measures as below

Measure = CALCULATE(COUNT(Sheet6[shipping_code]),ALLEXCEPT(Sheet6,Sheet6[date]))

Measure 2 = CALCULATE(COUNT(Sheet6[shipping_code]),ALLEXCEPT(Sheet6,Sheet6[date],Sheet6[shipping_code]))

Measure 3 = [Measure 2]/[Measure]

12.png

 

14.png

15.png

Best Regards

Maggie