Forum Discussion

Stemar_Aubert's avatar
Stemar_Aubert
Resolver I
5 years ago
Solved

Matrix Visual: Allselected and subcategories.

Hello everyone,

 

Consider the following dataset:

 

REGIONCOUNTRYPRODUCT 1PRODUCT 2PRODUCT 3ITEMSDATE
ASIACHINASEDANA3GPS101.01.2000
EUROPEPORTUGALCOUPEA7AUTODRIVE101.01.2000
AMERICAMEXICOSEDANA3NONE101.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 2PRODUCT 3RATIO
A3-100%
-GPS50%
-NONE50%

 

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 2PRODUCT 3RATIO
A3-50%
-GPS25%
-NONE25%
A7-50%
-AUTODRIVE50%

 

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.

Need Help
  • Stemar_Aubert's avatar
    Stemar_Aubert
    5 years ago

    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.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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's avatar
      Stemar_Aubert
      Resolver I
      5 years ago

      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.