Matrix Visual: Allselected and subcategories.

[Stemar_Aubert]

Hello everyone,

Consider the following dataset:

REGION	COUNTRY	PRODUCT 1	PRODUCT 2	PRODUCT 3	ITEMS	DATE
ASIA	CHINA	SEDAN	A3	GPS	1	01.01.2000
EUROPE	PORTUGAL	COUPE	A7	AUTODRIVE	1	01.01.2000
AMERICA	MEXICO	SEDAN	A3	NONE	1	01.01.2000

I have geographical and categorical dimensions. My goal is to calculate the ratio of each PRODUCT 3 against PRODUCT 2

Yield would be like this :

PRODUCT 2	PRODUCT 3	RATIO
A3	-	100%
-	GPS	50%
-	NONE	50%

However, under the constrain of having slicers based on both geo and categorical dimensions, I did not find a satisfying measure.

Currently, I have this (simplified form, the original one includes time intelligence filters) :

RATIO =
DIVIDE (
    CALCULATE ( SUM ( 'TABLE'[ITEMS] ) ),
    CALCULATE ( SUM ( 'TABLE'[ITEMS] ), ALLSELECTED ( 'TABLE' ) )
)

While this gives me the proper results if I slice down to PRODUCT 2 level, in a matrix visual, it gives me the following output :

PRODUCT 2	PRODUCT 3	RATIO
A3	-	50%
-	GPS	25%
-	NONE	25%
A7	-	50%
-	AUTODRIVE	50%

To clarify, below are actuals Results. This is a matrix visual, and first column has a hierarchy of PRODUCT 2 and PRODUCT 3.

As you can see, PRODUCT 2 sums to a total of 100%, and subtotals to each respective category participation to said total. 

Desired output shows each bold number at 100%. Total result doesn't have any significance and can be removed. PRODUCT 3 totals should sum to 100%.

Thanks for any help.

[parry2k]

@Stemar_Aubert glad you figured it out. cheers!!

[parry2k]

@Stemar_Aubert I think you need to change your measure to:

RATIO =
DIVIDE (
    CALCULATE ( SUM ( 'TABLE'[ITEMS] ) ),
    CALCULATE ( SUM ( 'TABLE'[ITEMS] ), ALLSELECTED ( 'TABLE'[Product3] ) )
)

✨ Follow us on LinkedIn

Check 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.⚡

[Stemar_Aubert · Accepted solution]

Hi @parry2k 

Thanks for the insights. Actually, I found out there was a slicer put out behind the visual which was impacting it, hence I could never reach 100%.

Lesson learned !

To handle this, I have set to use another parameter in the Calculate statement, to eliminate what is out of scope based on the slicer.

I could then swap the ALLSELECTED with ALLEXCEPT, and I now have the desired results.