Stacked Column Chart with Aggregated Data

[bga1]

Hi!

I have two tables linked with the tblOrder_ID
tblOrder

ID

1

2

3

tblOrderTask

tblOrder_ID	DeltaDays
1	-4
1	0
1	5
2	4
2	0
3	2
3	2
3	6
3	8

It should consider the minium of DeltaDays for each order.
If the minimum DeltaDays is <0 then count the order to the blue group.
If the minimum DeltaDays is =0 then count the order to the dark blue group.
If the minimum DeltaDays is >0 then count the order to the orange group.

In the Stacked Column Chart it should show the Amount of orders for each group

I think that I have to create some kind of measure but so far I was not successfull

Thank you very much for your help!

[Anonymous]

Hi @bga1 ,

You can try this:

Create calculated column in tblOrderTask: 

Column = IF(tblOrderTask[DeltaDays] < 0 ; 0; IF(tblOrderTask[DeltaDays] = 0; 1; IF(tblOrderTask[DeltaDays] > 0; 2)))

Create 3 measures: 

Less = CALCULATE(COUNTROWS(tblOrderTask); tblOrderTask[Column] = 0)

Equal = CALCULATE(COUNTROWS(tblOrderTask); tblOrderTask[Column] = 1)

More = CALCULATE(COUNTROWS(tblOrderTask); tblOrderTask[Column] = 2)

Then put all 3 measures in Value of a Stacked Column Chart

Hope this helps!

Br, 

Adrian

[bga1]

Thanks @Anonymous !

Your post helped me a lot.

Ther's one more problem I think.

I have to know the amount of rows in the order table. If I count in tblOrderTask I think I get the amount of Tasks and not orders.

Less = CALCULATE(COUNTROWS(tblOrderTask); tblOrderTask[Column] = 0)

I tried It like that, but it only shows the total amount of rows in tblOrder

Less = CALCULATE(COUNTROWS(tblOrder); tblOrderTask[Column] = 0)

Any ideas?

[Anonymous]

@bga1 

Try changing the calculated colum to:

Column = CALCULATE(IF(MIN(tblOrderTask[DeltaDays]) < 0 ; 0; IF(MIN(tblOrderTask[DeltaDays]) = 0; 1; IF(MIN(tblOrderTask[DeltaDays]) > 0; 2))); ALLEXCEPT(tblOrderTask; tblOrderTask[tblOrder_ID]))

Then change the measures to distinctcount on the Order_ID: 

Less = CALCULATE(DISTINCTCOUNT(tblOrderTask[tblOrder_ID]); tblOrderTask[Column] = 0)

/Adrian

[bga1]

So, for my example in the OP it should show 1/1/1 because ther's one order in each group.

[AiolosZhao · Accepted solution]

Hi @bga1 ,

Based on your data, I created below chart, hope that's what you want:

I created 4 measure:

Measure 6 = CALCULATE(MIN('Table (3)'[DeltaDays]),ALLEXCEPT('Table (3)','Table (3)'[tblOrder_ID]))

BLUE = CALCULATE(DISTINCTCOUNT('Table (3)'[tblOrder_ID]),FILTER('Table (3)',[Measure 6] < 0))

DARK BLUE = CALCULATE(DISTINCTCOUNT('Table (3)'[tblOrder_ID]),FILTER('Table (3)',[Measure 6] = 0))

ORANGE = CALCULATE(DISTINCTCOUNT('Table (3)'[tblOrder_ID]),FILTER('Table (3)',[Measure 6] > 0))

Please try.

Aiolos Zhao

[bga1]

@AiolosZhao 

Thank you so much for the support so far

I have one additional question:

Measure 6 = CALCULATE(MIN('Table (3)'[DeltaDays]),ALLEXCEPT('Table (3)','Table (3)'[tblOrder_ID]))

Lets assume we have another column [Status] in Table (3).

What do I have to change in this measure, when I want to only consider values for this measure that have [Status] 1 or 8?

[AiolosZhao]

Hi @bga1 ,

Please try below measure:

CALCULATE(MIN('Table (3)'[DeltaDays]),ALLEXCEPT('Table (3)','Table (3)'[tblOrder_ID]),OR(Status = 1,Status = 8))

Aiolos Zhao