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

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

      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