Forum Discussion

david_63's avatar
david_63
Frequent Visitor
4 years ago

clustered column

Hello, first post and VERY new to Power BI (currently 8 hours in using desktop version). 

 

(My problem looks similar to Solved: Clustered Column Chart Visual - Microsoft Power BI Community, however I don't understand the solution).  

 

I have created a data model consisting of five related data tables and added another three for creating sort orders for some of the visuals – all from Excel files. 

It appears that I have successfully created most of the relationships between these tables – the slicers and charts seem to be reacting properly (this is such an awesome product!)

 

With one frustrating exception...

 

I have a clustered column graph which isn’t playing nicely with the others... I’m pretty sure it’s something in the way I’m connecting/relating some fields but stuffed if I can work out what I’m doing wrong...

 

One of the data tables contains two fields of interest:

  • Due Date, and
  • Original Due Date

 

I have created two calculated “overdue-ness” columns in the data table:

  • ‘level curr due’ (formula based on ‘status’ column, ‘due date’ and today’s date)
  • ‘level orig due’ (formula based on ‘status’ column, ‘original due date’ and today’s date)

The formula performs the following – if the status of the record is ‘complete’ then returns ‘NA’, if blank then returns ‘not assigned’, otherwise determines buckets of overdue-ness based on difference between the date and today(), ie “1m”, “3m”, “6m”, etc. 

(Note: I have verified the formula by exporting the calculated values and comparing to manually calculated in Excel – these all match as expected...)

 

I have also created a table with the forementioned buckets and their sort order – this also seems to be ordering the fields as required...

 

However...

 

When plotting the data, I get the SAME count for both date fields in each of the buckets – and totals well short of the number of records, as follows:

 

Power BI cluster chart result:

 

This is a far cry from the ‘actual’ results – though some seem to match for some reason:

 

EXPECTED (true) results:

 

 

When I create a standalone chart of each single data set, they appear as they should.  When I try to combine them on the one chart, it all goes awry. 

 

 

 

Fig 1:

I am joining the ‘due group’ in ‘due_sort’ table to each of the calculated fields in the table of interest.  

Note: I have tried joining only to one field, changing the ‘due_sort’ table to include duplicate ‘due_group’ fields and creating a second duplicate ‘due_sort’ table completely to join them to the data table independently.  All with the same result. 

 

 

 

Fig 2:

In the chart, I drop ‘due group’ in the axis, and the count of level curr due and count of level orig due in the values field

 

 

 

Can someone please point me in the right direction??

 

Much appreciated...

 

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    david_63 

     

    Welcome !! Good luck with your Power BI Journey. 

     

    as per the relationship diagram, you have two relations and for your knowledge, you can have only one active relationship between the same tables, in your case, the solid line shows the active relationship and the dotted line shows the inactive relationship. So any time you visualize the data, it will use an active relationship, to use an inactive relationship, you have to create a measure and then in that measure make an inactive relationship active:

     

    Sum Due Date = SUM ( Table[Amount] ) //this will use active relationhip

Sum Original Due Date = 
CALCULATE ( 
    [Sum Due Date],
    USERELATIONSHIP ( Table[Original Due Date], DueDateTable[Due Date] ) //this will make inactive relationship to active
)

     

    use these measures in the visual and it will work as expected.

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • david_63's avatar
      david_63
      Frequent Visitor
      4 years ago

      Thanks parry2k.  Where do I put these 'measures' - in the main table or the 'due_sort' table? 

       

      (and if you don't mind - for my own understanding - what are the two measures doing in a Power BI sense?  I am really new with all this).  

       

      kind regards... 

       

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    david_63 no problem. Measures are global to the report, you can put in any table, doesn't matter.

     

    I used example table and column name in my measure and you need to change these as per model.

     

    The first measure is simply doing sum of amount column in a table called Table (think of SUM function in excel), 2nd measure is using first measure and making inactive relationship to active. 

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • david_63's avatar
      david_63
      Frequent Visitor
      4 years ago

      Hi and thanks again parry2k. I'm obviously missing something as nothing changed - I'm guessing I'm not interpreting fields and tables as I should in the resultant Measures formulas. Please bear with me for one more shot at this... 
      Could you please re-post the formula as close to the names below...? 
      (and do I need to subsequently modify relationships or charts?) 


      Table named 'DATA' 20 fields, which includes: 

      Field 'original due' = 30 JUNE 2021 
      Field 'curr due' = 31 DEC 2021 

       

      I added calculated fields to the DATA table - this results in following for the above fields (similarly for all fields in DATA table) 
      Field 'level orig' = "less 6m" 
      Field 'level curr' = "not due" 


      Table named 'SORT' (2 columns) to determine order of the date buckets: 
      not due      1
      less 1m       2
      less 3m       3
      less 6m       4
      more 6m    5


      Resulting plot required is count of 'level orig' against 'level curr' by date bucket in clustered column chart. 

       

      Thanks again...