Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure shows only total

Hello,    I am making a dashboard where I want to calculate the average sales per day per product.    I have one table ('EAN') that contains the article number [EAN13] and the title [Title short]...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    This as to do with the context of your measure, when you are making the visual based on the EAN table you cannot make the calculation based on the Sales[EAN] code because the row context is different, what happens is that you are making the filter context for the visual based on one value from one table but then calculating the measure on the Sales side.

     

    This is even more pressing since you have a one to many relationship going from EAN to Sales so when you calculate a value from the Sales to the EAN since the relationship is going the other way is like you don't have any relation in place so it returns a single value for all the rows.

     

    In your measure the allexcept part is redundant since the filter context comes from the EAN redo the measure to:

    Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sales] ) ; 
            FILTER ( 
                 'Sales/Day'  ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable_1] ) ; 
            FILTER ( 
                 'Sales/Day' ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )

    As you can see below the measure Sales/Day All Time 2 (adjusted gives the correct value).

     

     

    See attach PBIX file.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi Anonymous ,

 

Measures are based on context and depending on the way you setup you measure you have different results, your model is not easy to replicate by hand but if you can share a mockup that would be great.

 

Even without making any mockup try to change the reference to EAN table on your allselected to the EAN13 column replace your measure by:

 

Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Verkopen] ) | 
            FILTER ( 
                ALLEXCEPT( 'Sales/Day' | 'Sales/Day'[EAN]) | 
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  |
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) | 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable 2] ) | 
            FILTER ( 
                ALLEXCEPT( 'Sales/Day' | 'Sales/Day'[EAN]) | 
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) |
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) | 
                EAN[EAN13] <> BLANK()
            )
        )

I have highlithed in bold the changes if this doesn't work please as I say before share some mockup file.

 

Regards,

MFelix 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi MFelix 

    Thanks for your quick reply. Changing the measure formula does not seem to work unfortunately. 

     

    I attached a mockup model for you. 

    PBIX File

    Excel file with data

     

    Looking forward to hearing from you/

    Thanks again for your help.

     

    Patrick

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago

      Hi Anonymous ,

       

      This as to do with the context of your measure, when you are making the visual based on the EAN table you cannot make the calculation based on the Sales[EAN] code because the row context is different, what happens is that you are making the filter context for the visual based on one value from one table but then calculating the measure on the Sales side.

       

      This is even more pressing since you have a one to many relationship going from EAN to Sales so when you calculate a value from the Sales to the EAN since the relationship is going the other way is like you don't have any relation in place so it returns a single value for all the rows.

       

      In your measure the allexcept part is redundant since the filter context comes from the EAN redo the measure to:

      Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sales] ) ; 
            FILTER ( 
                 'Sales/Day'  ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable_1] ) ; 
            FILTER ( 
                 'Sales/Day' ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )

      As you can see below the measure Sales/Day All Time 2 (adjusted gives the correct value).

       

       

      See attach PBIX file.

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi MFelix,

         

        You are a hero, it works perfectly! I marked your post as a solution! 

        Have a great weekend.

         

        Patrick