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_IDDeltaDays
1-4
10
15
24
20
32
32
36
38

 

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!

 
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

7 Replies

  • 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