Subtotal issue using summarize and sumx

[souvik900766]

Hi,
My measure using sumx and summarize showing values at market level 0.
I wanted to add all the market numbers to get the OU total ,the markets are coming zero and then add all the OU totals to get the Grand total.
My OU and Market come from same table.

My expected result is :- Correct total at OU level(by adding the markets numbers and the respective market numbers should also show), in Measure " Ind Mix Imact on shares PY ". The numbers in this measure at market level is showing correct.
I tried with the Ind Mix Imact on shares PY (correct total ) Measure but it showing wrong OU Total numbers and ZEROS in market level.

Here is the link to the file :- https://www.dropbox.com/s/7oq1viv0f7of033/Total%20error.pbix?dl=0

Any help will be appreciated.
Thanks in advance.

[AlexisOlson · Accepted solution]

This is a tough one. I suspect things are going awry with you using ALLSELECTED (from your "Industry TOP40 Base" measures) inside of iterators SUMX/SUMMARIZE.

Quoting from The definitive guide to ALLSELECTED:

As described here, the behavior of ALLSELECTED is easier to understand when one is more familiar with shadow filter contexts. With that said, ALLSELECTED is a very complex function because of the presence of shadow filter contexts, and because their interaction with the explicit filter contexts makes it very hard to elaborate on the results. What we typically teach users during our trainings is to use ALLSELECTED to retrieve the query context – that is, the context under which a pivot table or a report is executed – if and only if no iteration is happening. If there are any number of iterations, users are encouraged to avoid using ALLSELECTED because the results are almost unpredictable. In reality, results are very complex to understand. This article presents all the tools needed to understand the behavior of ALLSELECTED. That said, we would not want to have to perform all these complex reasoning steps every time we need to debug a measure. Thus, the suggestion still stands: ALLSELECTED should not be used inside an iteration, unless the user has established a very clear understanding of what they are doing and there is a strong need for it. In most scenarios, variables make it possible to avoid using ALLSELECTED inside an iteration. Variables are strongly suggested to that effect.

I don't fully understand your measures but you might be able to fix your problem by computing the ALLSELECTED bits outside of the SUMX iterator since it's iterating over Market and they include ALLSELECTED ( GTC[Market] ).

I don't think this actually works but it demonstrates the sort of refactoring I mean:

Ind Mix Imact on shares PY (Correct Total) =
VAR IndustryTop40BaseCY = [Industry TOP40 Base CY]
VAR IndustryTop40BasePY = [Industry TOP40 Base PY]
RETURN
    SUMX (
        VALUES ( GTC[Market] ),
        ( DIVIDE ( [Industry CY], IndustryTop40BaseCY ) -
          DIVIDE ( [Industry PY], IndustryTop40BasePY ) )
        * 100 * [Share within Ind Market PY]
    )

[souvik900766]

Hi @AlexisOlson ,

Thanks for the help. It worked !
You proved to my Santa this christmas.
Merry Christmas !