Forum Discussion

sagarp's avatar
sagarp
Regular Visitor
2 years ago
Solved

Line chart based on selected year in slicer

I have two different excel sheets with following tables,
1. Inbound

Fiscal YearFiscal WeekDomesticImport
20241126
20242139
20243108
20244154

 

2. Outbound

Fiscal YearFiscal WeekStoreWholesale
20241122
20242156
20243109
20244146

 

I need to make visual of line charts, which display total inbound (import + domestic) and total outbound (store + wholesale) on y-axis (2 different lines) and Fiscal week on x-axis. Now I nut to put the slicer with Fiscal year. So if I pick any fiscal year (ex-2024) then it will diplay line charts of total outbound and invound vs fiscal week of 2024.
I tried to do it, but it just display the data of total inbound if I put fiscal year of Inbound data in Slicer.
Cold you please help me with this?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi sagarp ,

     

    Thanks for the reply from lbendlin , please allow me to provide another insight: 

     

    Create measure.

     

    In_True =

SUMX ( 'Inbound', [Domestic] ) + SUMX ( 'Inbound', [Import] )

     

     

    Out_True =

VAR _Store =

    SUMX (

        FILTER (

            ALL ( Outbound ),

            'Outbound'[Fiscal Year] = MAX ( 'Inbound'[Fiscal Year] )

                && 'Outbound'[Fiscal Week] = MAX ( 'Inbound'[Fiscal Week] )

        ),

        'Outbound'[Store]

    )

VAR _Wholesale =

    SUMX (

        FILTER (

            ALL ( Outbound ),

            'Outbound'[Fiscal Year] = MAX ( 'Inbound'[Fiscal Year] )

                && 'Outbound'[Fiscal Week] = MAX ( 'Inbound'[Fiscal Week] )

        ),

        'Outbound'[Wholesale]

    )

RETURN

    _Store + _Wholesale

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Depends a bit on how large your tables are, but in general you want to unpivot both and then append them.

     

     

    Then you can create two measures

    Inbound = CALCULATE(sum(Combined[Value]),Combined[Attribute] in {"Domestic","Import"})

Outbound = CALCULATE(sum(Combined[Value]),Combined[Attribute] in {"Store","Wholesale"})

     

    and then you can plot both.

     

     

     

     

    • sagarp's avatar
      sagarp
      Regular Visitor
      2 years ago

      lbendlinThank you for your response and the solution provided. I actually have fiscal weeks 1 to 52 for 2023, 2024, and 2025. Your solution worked for me, but I decided to use Clara's solution as it is more suitable for my needs.

      Thanks again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sagarp ,

     

    Thanks for the reply from lbendlin , please allow me to provide another insight: 

     

    Create measure.

     

    In_True =

SUMX ( 'Inbound', [Domestic] ) + SUMX ( 'Inbound', [Import] )

     

     

    Out_True =

VAR _Store =

    SUMX (

        FILTER (

            ALL ( Outbound ),

            'Outbound'[Fiscal Year] = MAX ( 'Inbound'[Fiscal Year] )

                && 'Outbound'[Fiscal Week] = MAX ( 'Inbound'[Fiscal Week] )

        ),

        'Outbound'[Store]

    )

VAR _Wholesale =

    SUMX (

        FILTER (

            ALL ( Outbound ),

            'Outbound'[Fiscal Year] = MAX ( 'Inbound'[Fiscal Year] )

                && 'Outbound'[Fiscal Week] = MAX ( 'Inbound'[Fiscal Week] )

        ),

        'Outbound'[Wholesale]

    )

RETURN

    _Store + _Wholesale

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • sagarp's avatar
      sagarp
      Regular Visitor
      2 years ago

      AnonymousThank you so much for providing the solution. It works perfectly for me. This is exactly what I was looking for.