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

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

     

     

     

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

      Sorry, the codes seems to be deleted:

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


    

//VAR Usg12=CALCULATE(TotRes,filter(ALLSELECTED(C_GOODS),[1st Y_M WoUsg]=12))
return
TotRes

       

      (Your Code)

       

      1st 3_M WoUsg = 
VAR FMnCount3 =
UNION(
   ROW("MTV",[-1M]),
   ROW("MTV",[-2M]),
   ROW("MTV",[Sold Prod Qnty])
  )

VAR TotRes=calculate(COUNTAX ( FMnCount3, [MTV] ), FILTER ( FMnCount3, [MTV] <> BLANK() ))
    

Return
TotRes

      Old code

       

       

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

        George1973 

        Whay did you replacrme COUNTAX with COUNTX?