Forum Discussion

SG0831's avatar
SG0831
New Member
1 year ago
Solved

Report Filter Causing Data in matrix to be incorrect

Hi there,

 

I have a pbix file showing data in a matrix with summarised data at a certain sub-total level - in this case at Code level. There is a lower level of Customer Code but my matrix is summarised with an Outcome at Code level.

What I want to do is filter the matrix for all the rows where the Outcome is only “Both SV and TPV Exceed Allowed %” but at the summarised level. Outcome is a Calculated field and I am using the report filters to apply this.

 

 

 

So effectively end up with just the two rows highlighted in Green below

 

 

 

However when I apply the filter to the Case Id, Customer Code, the percentages change and I end up with the wrong data. 

 

As per the screenshot, my Total SV should be 18,620 but after filtering it goes to 18,000 and my Total TPV should be 28,620 but after filtering it goes to 18,000.

I did manage to get it to 28,620 but then if I change the filter it all goes haywire again.

I am presuming I need a summarize variable or something or an ALLEXCEPT or a combination of items??? but again, I am lost 

 

I can't find where to upload my pbix file??

But my variables are like this:

Total TPV =
CALCULATE(
    sum(Sheet1[Value]),
    FILTER(ALLSELECTED('Sheet1'),
    'Sheet1'[Customer Code] in ALLSELECTED(Sheet1[Customer Code])))
 
I then tried
Total TPV 2 =
CALCULATE(
    sum(Sheet1[Value])
    ,removefilters(Sheet1[Case ID])
    )

Total TPV 3 =
CALCULATE(
    sum(Sheet1[Value])
    ,removefilters(Sheet1[Case ID],Sheet1[Code],Sheet1[Classification],Sheet1[Sub Classification])
    )
 

It seems the application of the filter is altering the calculation of the total SV and Total TPV fields which is causing my % to go out of whack.

 I have tried REMOVEFILTERS and that works to a degree but then I lose the filter that I need. I thought maybe I could remove it and then replace it, but that didn't work either. I can't see where to attach my dummy data and the pbix file ?? Apologies!

Your assistance is greatly appreciated!

  • SG0831's avatar
    SG0831
    1 year ago

    Hi all,

    Thanks so much for the help.

    The solution ended up being a lot simpler, just implement a proper star-schema.

    Unfortunately my data set is enormous so I am looking into other options as well, including a stored procedure solution.

     

    Thanks anyway 

    Sarah

6 Replies

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    Hi SG0831 ,

    You can try creating a calculated column  then use this as a visual level filter and set flag=1

    Filtered Outcome Flag =
IF (
    ISINSCOPE(Sheet1[Code]),
    IF (
        SELECTEDVALUE(Sheet1[Outcome]) = "Both SV and TPV Exceed Allowed %",
        1,
        0
    )
)

     To fix your totals- 
    try:

    Total TPV (Fixed) =
CALCULATE (
    SUM(Sheet1[Value]),
    REMOVEFILTERS(Sheet1[Customer Code], Sheet1[Case ID])
)


    Hope this helps!

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    Hi SG0831 ,
    Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

    Thank You

    • SG0831's avatar
      SG0831
      New Member
      1 year ago

      Hi all,

      Thanks so much for the help.

      The solution ended up being a lot simpler, just implement a proper star-schema.

      Unfortunately my data set is enormous so I am looking into other options as well, including a stored procedure solution.

       

      Thanks anyway 

      Sarah

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    Hi SG0831 ,

    Thanks for the update.
    We would be closing this thread. If you have any issues feel free to open a separate ticket and we would be happy to assist you.

    Thank You