Change hierarchy level in matrix visualization

[Anonymous]

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! 🙂 

[Anonymous · Accepted solution]

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]

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]

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

[Anonymous]

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 🙂

[parry2k]

@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]

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 🙂