Percentage Of Total by Category Over Time

[TheGreatestGoat]

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:

date	order_id	shipping	shipping_code
5/08/2018	747127111111	16.99	E2222
5/08/2018	747148111111	7.99	S1111
5/08/2018	747144111111	16.99	E2222
5/08/2018	746663111111	0	F1000
4/08/2018	746297111111	0	F1000
4/08/2018	746717111111	7.99	S1111
4/08/2018	746781111111	0	F1000
4/08/2018	746486111111	0	F1000

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! 

[v-juanli-msft · Accepted solution]

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]

Best Regards

Maggie

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.