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?

  • 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

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