Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Change hierarchy level in matrix visualization

Hi!

 

I need to create a Matrix visual (or  a visual table looking that does the same thing) using 2 levels of hierarchy. This is the data:

 

 

And I need to display the total of all (that's easy), but below that just ONE of the companies (Nike in my example below) and then the cities, but showing the total amount including the items sold in the other company.

 

On the left you can see what I get, that it's Nike and within that level I have the cities, but what I want is the rigth picture that I made in excel as an example, which is one company and then the total per city in both nike and adidas.

 

 

Is there a way to make that possible?

 

Thank you in advance! 🙂 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous , 

    Please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure =
VAR _value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[City] = SELECTEDVALUE ( 'Table'[City] )
                && 'Table'[Label] = SELECTEDVALUE ( 'Table'[Label] )
        )
    )
VAR _NIKE =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Label] = SELECTEDVALUE ( 'Table'[Label] )
                && 'Table'[City] = SELECTEDVALUE ( 'Table'[City] )
        )
    )
VAR result =
    IF (
        HASONEVALUE ( 'Table'[City] )
            && MAX ( 'Table'[Company] ) = "NIKE",
        _NIKE,
        IF (
            HASONEVALUE ( 'Table'[City] )
                && MAX ( 'Table'[Company] ) = "Adidas",
            _NIKE,
            SELECTEDVALUE ( 'Table'[Value] )
        )
    )
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Label] = SELECTEDVALUE ( 'Table'[Label] )
                && 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] )
        )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[City] ), result, _a )

     

    If I have misunderstood your meaning, please provide more details with your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous , 

    Please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure =
VAR _value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[City] = SELECTEDVALUE ( 'Table'[City] )
                && 'Table'[Label] = SELECTEDVALUE ( 'Table'[Label] )
        )
    )
VAR _NIKE =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Label] = SELECTEDVALUE ( 'Table'[Label] )
                && 'Table'[City] = SELECTEDVALUE ( 'Table'[City] )
        )
    )
VAR result =
    IF (
        HASONEVALUE ( 'Table'[City] )
            && MAX ( 'Table'[Company] ) = "NIKE",
        _NIKE,
        IF (
            HASONEVALUE ( 'Table'[City] )
                && MAX ( 'Table'[Company] ) = "Adidas",
            _NIKE,
            SELECTEDVALUE ( 'Table'[Value] )
        )
    )
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Label] = SELECTEDVALUE ( 'Table'[Label] )
                && 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] )
        )
    )
RETURN
    IF ( HASONEVALUE ( 'Table'[City] ), result, _a )

     

    If I have misunderstood your meaning, please provide more details with your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

       

      Sorry for the late response. I couldn't check the code until now.

      That's exactly what I wanted!! I didn't think it could be possible, you are brilliant!

      I really appreciate that you have taken the time to help me. 😊

       

      Thank you very much! 😊

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous why there is no adidas in the expected output?

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi parry2k 

       

      This is an example. In my real data, I need to show a category that would be "Nike" here, to show the total items sold no matter the city. The cities that appear below, if you see, they are the sum of both Nike an adidas items sold. For instance, Madrid is 400 (100+300) in the first column (Alicia).

      Thank you 🙂

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous well should explain the logic based on sample data. as I asked, will the same happen for adidas?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi parry2k 

       

      Sorry, I didn't understand your question the first time (English is not my mother language).

      Yes, for Adidas would the same, the total for adidas and then the cities would have the same result as it's the sum of both again (here a pic of the result)

       

       

      I hope I explained it properly now.

      Thank you very much 🙂