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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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'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?