Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago
Solved

Summarization for Measure Does not Work

Hi Guys, I have a measure counting sales ever existence number of month having sales activity within last 3 months.   1st 3_M WoUsg = VAR FMnCount3 = UNION( ROW("MTV",[-1M]), ROW("MTV",[...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi George1973 

    you may try 

    1st 3_M WoUsg =
VAR FMnCount3 =
    UNION (
        ROW ( "MTV", [-1M] ),
        ROW ( "MTV", [-2M] ),
        ROW ( "MTV", [Sold Prod Qnty] )
    )
RETURN
    SUMX (
        VALUES ( Table[Code] ),
        CALCULATE ( COUNTAX ( FMnCount3, [MTV] ), FILTER ( FMnCount3, [MTV] > 0 ) )
    )
  • George1973's avatar
    George1973
    4 years ago

    tamerj1 
    I think I found an alternative solution. At least for the final purpose, it is more than enough:

    Month Usage Sum = 

VAR FMG=ADDCOLUMNS(allselected(C_GOODS[პროდუქციის კოდი]),"Pr_Code",C_GOODS[პროდუქციის კოდი],"3M_Usg",[1st 3_M WoUsg],"12M_Usg",[1st Y_M WoUsg])
VAR Sum12M=sumX(filter(FMG,[12M_Usg]=12),[3M_Usg])
VAR Sum11M=sumX(filter(FMG,[12M_Usg]=11),[3M_Usg])
VAR Sum10M=sumX(filter(FMG,[12M_Usg]=10),[3M_Usg])
VAR Sum9M=sumX(filter(FMG,[12M_Usg]=9),[3M_Usg])
VAR Sum8M=sumX(filter(FMG,[12M_Usg]=8),[3M_Usg])
VAR Sum7M=sumX(filter(FMG,[12M_Usg]=7),[3M_Usg])
VAR Sum6M=sumX(filter(FMG,[12M_Usg]=6),[3M_Usg])
VAR Sum5M=sumX(filter(FMG,[12M_Usg]=5),[3M_Usg])
VAR Sum4M=sumX(filter(FMG,[12M_Usg]=4),[3M_Usg])
VAR Sum3M=sumX(filter(FMG,[12M_Usg]=3),[3M_Usg])
VAR Sum2M=sumX(filter(FMG,[12M_Usg]=2),[3M_Usg])
VAR Sum1M=sumX(filter(FMG,[12M_Usg]=1),[3M_Usg])
VAR Sum0M=sumX(filter(FMG,[12M_Usg]=0),[3M_Usg])

Return
Sum11M

    First it calculates the grand total sum for the the months with existing sales and then, I can refer to the SUMS with the second criteria from [12M_Usg].. be it 1, 2,3... or 12..
    The final result has been achieved. That's all I wanted! :))) Thanks a lot again1

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi George1973 

you may try 

1st 3_M WoUsg =
VAR FMnCount3 =
    UNION (
        ROW ( "MTV", [-1M] ),
        ROW ( "MTV", [-2M] ),
        ROW ( "MTV", [Sold Prod Qnty] )
    )
RETURN
    SUMX (
        VALUES ( Table[Code] ),
        CALCULATE ( COUNTAX ( FMnCount3, [MTV] ), FILTER ( FMnCount3, [MTV] > 0 ) )
    )
  • George1973's avatar
    George1973
    Icon for Helper V rankHelper V
    4 years ago

    tamerj1 

    Again, no improvement 😞

    Some logic, As I wrote you before, is that, the mistake happens when there is a blank result in [Sold Prod Qnty] result, but not always.. But why it is so, I have no clue..
    Ok, we have spent a whole day in solving it.. I do not want to bother you anymore.

    Thank you very much again for your support and proffesionalism. I do realy appriciate it.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Try wrap the formula with KEEPFILTERS

      • George1973's avatar
        George1973
        Icon for Helper V rankHelper V
        4 years ago

        Like that?

        Month Usage Sum = 
VAR FMnCount3 =
    UNION (
                ROW ( "MTV", [Sold Prod Qnty] ),
                ROW ( "MTV", [-1M] ),
                ROW ( "MTV", [-2M] )
    )
RETURN
   SUMX (
    VALUES ( C_GOODS[პროდუქციის კოდი] ),
    CALCULATE (
        COUNTAX ( FMnCount3, [MTV] ),
        KEEPFILTERS(
        FILTER (
            UNION (
                ROW ( "MTV", [Sold Prod Qnty] ),
                ROW ( "MTV", [-1M] ),
                ROW ( "MTV", [-2M] )
            ),
            [MTV] > 0
        )
    )
)
   )

        Still does not work 😞

         

  • George1973's avatar
    George1973
    Icon for Helper V rankHelper V
    4 years ago

    Yes, That's itz.. It works! Thanks a lot

  • George1973's avatar
    George1973
    Icon for Helper V rankHelper V
    4 years ago

    Hi tamerj1 again,

     

    Sorry for disturbing you again, but the measure gives error in some cases:

     

    The column, Marked with yellow color is the right (old) result and the column with blue, is the new measure results.. as you can see, there are blanks, instead of the right figures.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      George1973 

      Can you please paste the code you have used in a reply? Are sure you are using the correct column reference in the code? Is there any other column involved in the tsummary table?

      • George1973's avatar
        George1973
        Icon for Helper V rankHelper V
        4 years ago

        tamerj1 ,
        Here is the code new code (Developed by You):

         

        And Here is the old code:

         

        Please note that:

        The marked one is the reference to Product-Code