Forum Discussion

tvilla's avatar
tvilla
New Member
4 years ago
Solved

Barchart with constant line

Hi everybody,

I'm stucked on this issue from a while and realized I need help to go further. 

 

I have two table with the follow headers:

1) ID, Date (yyyy-mm-dd), Type1, Type2, Value1

2) ID, Month(yyyy-mm-01), Value2

 

I would like to build a barchart with the follow features:

- the barchart shows the daily value1 for each ID in a specific month (both selectable through a filter)

- the barchart has a constant line showing value2 for the selected ID and month

- I need to allow filter by type1 and type2 (when a filter is selected the bar should go down while the constant line has to remain stable or disappear at all)

 

I hope you can help me with this task and wait for your feedback,

Thank you!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI tvilla,

    #1, You can refer to the following blog to create relationships based on multiple table fields. (create a calculated column with year, month part from the date field and concatenate with id, then extract them to create a bridge table with unique value to mapping two table records)

    Relationship in Power BI with Multiple Columns - RADACAD

    How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

    #2, I'd like to suggest you create a measure formula to summary value based on current groups and use it as the constant line:

    measure =
VAR selected =
    MAX ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table2[Value2] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            YEAR ( [Date] ) = YEAR ( selected )
                && MONTH ( [Date] ) = MONTH ( selected )
        ),
        VALUES ( Table1[ID] )
    )

    #3, You can try to do 'unpivot column' on type fields to send them to attribute and value, then you can simply use one filter to filter across multiple table fields.

    Unpivot columns - Power Query | Microsoft Docs

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI tvilla,

    #1, You can refer to the following blog to create relationships based on multiple table fields. (create a calculated column with year, month part from the date field and concatenate with id, then extract them to create a bridge table with unique value to mapping two table records)

    Relationship in Power BI with Multiple Columns - RADACAD

    How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

    #2, I'd like to suggest you create a measure formula to summary value based on current groups and use it as the constant line:

    measure =
VAR selected =
    MAX ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table2[Value2] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            YEAR ( [Date] ) = YEAR ( selected )
                && MONTH ( [Date] ) = MONTH ( selected )
        ),
        VALUES ( Table1[ID] )
    )

    #3, You can try to do 'unpivot column' on type fields to send them to attribute and value, then you can simply use one filter to filter across multiple table fields.

    Unpivot columns - Power Query | Microsoft Docs

    Regards,

    Xiaoxin Sheng