Forum Discussion

bga1's avatar
bga1
Frequent Visitor
6 years ago
Solved

Stacked Column Chart with Aggregated Data

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
bga1
Frequent Visitor
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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