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?

bga1's avatar
bga1
Frequent Visitor
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      Anonymous 

      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?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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