Forum Discussion

dreyz64's avatar
dreyz64
Helper III
6 years ago
Solved

Help - Filtering a visualization based on a parameter and a measure not working

Hi everyone,   I now have been struggling for a while with this and I am going mad as I'm sure this is something simple. I have created a table where I have various products, their actual sales, f...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi dreyz64 ,

     

    This as to do with context I have made a single measure the actuals:

     

    Actuals_Final = SUMX(SUMMARIZE(Sheet1;Sheet1[DATE];"@ACTUALS";[ACTUALS2]);[@ACTUALS])

     

    This is additional on top of the ACTUALS2 so you need to have a intermidiate measure to calculate the values.

     

    Please check if is working correctly on the PBIX file.

     

    If it works you need to do a similar thing for forecast. and then use those measures on the Change %2

MFelix's avatar
MFelix
Super User
6 years ago

Hi dreyz64 ,

 

Is your parameter a % column (formatt as text) or a number formated as percentage?

 

If it's the last one you should compare with 1 and not with 100, because 100% is just a visual format the actual number is 1.

  • dreyz64's avatar
    dreyz64
    Helper III
    6 years ago

    Hi MFelix ,

    My parameter is an integer and then when I do my filter is do divide(parameter, 100) so in the end, it is between 0 and 1.

    But for my users they can directly input the whole number.

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      Hi dreyz64 ,

       

      can you please share a sample file? is difficul to see based on your image what are the formats values etc. used, and when using measure the context is the most important part.

       

      Changing the context of a measure can make very different result than what we are expecting.

      • dreyz64's avatar
        dreyz64
        Helper III
        6 years ago

        Unfortunately I cannot share a sample as I have confidential data and it would take me ages to make a sample report to share...

        I've managed to find a work around where instead of trying to use a Filter, I re-calculate my values 

        For example, i do: IF abs(%Change ) > Threshold (in %) then sum (actuals)

        Same for my forecast values.

        This allows me to get the below table.
        Now visually the table does what I wanted the filter to do. However the totals are completely messed up and I don't understand why 😞

        Example of formula: 

        Actuals = IF(abs([%Change_Calc])>=divide(Parameter[Parameter Value],100),
        sum('COT_ACT_Forecast Bridge'[ActVol]), 0)
        %Change_Calc =
        IF(
        sum('COT_ACT_Forecast Bridge'[ActVol])=0 && Sum('COT_ACT_Forecast Bridge'[Fcst])<>0, -1,
        IF(
        sum('COT_ACT_Forecast Bridge'[ActVol])<>0 && Sum('COT_ACT_Forecast Bridge'[Fcst])=0,1,
        DIVIDE((sum('COT_ACT_Forecast Bridge'[ActVol])-sum('COT_ACT_Forecast Bridge'[Fcst])),sum('COT_ACT_Forecast Bridge'[ActVol]),BLANK())
        )
        )