Forum Discussion

George1973's avatar
George1973
Helper 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",[-2M]),
   ROW("MTV",[Sold Prod Qnty])
  )

Return
CAlculate(COUNTAX(FMnCount3,[MTV]),FILTER(FMnCount3,[MTV]>0))

And it's fine. It's beeing calculated for each product.

But the problem is I can not sum the whole mesaure number.

As you can see, by rows I'm getting the right number,  but in the Column total I have no summary.
Even when I try to get a result in a "Card Visual" I have only 3 as result.

Please help in solving this issue.

Thanks in advance,



  • 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

25 Replies

    • George1973's avatar
      George1973
      Helper V
      4 years ago

      Hi tamerj1 ,

       

      Nice to hear from you again 🙂

      The calculation is being done byv Products (Here are the product codes):

       

      In this table "Code" field is for Product Codes.

      So, this measure (The last column) counts three previous months with existed sales per each product. The mesasure itself works fine as you can see.

      Why I need the summarization? It's the step: In order  to calculated total of [1st 3_M WoUsg] referenced to the previous column in the table "1st Y_M WoUsg".
      [      1st Y_M WoUsg] it's also the measure, very similar to the already mentioned one, just showing number of months with existed sales within the previous 12 months.
      Meaning:
      I want to sum of [1st 3_M WoUsg] with [1st Y_M WoUsg] criteria, like:
      - What is the sum of [1st 3_M WoUsg] content if [1st Y_M WoUsg]=1

      - What is the sum of [1st 3_M WoUsg] content if [1st Y_M WoUsg]=2

      - What is the sum of [1st 3_M WoUsg] content if [1st Y_M WoUsg]=3

        ....

      - What is the sum of [1st 3_M WoUsg] content if [1st Y_M WoUsg]=12



       

       

       

  • tamerj1's avatar
    tamerj1
    Community 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
      Helper 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
        Community Champion
        4 years ago

        Try wrap the formula with KEEPFILTERS

    • George1973's avatar
      George1973
      Helper 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
        Community 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?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    George1973 

    Try is simple by referring to old measure

    1st 3_M WoUsg new =
    SUMX ( VALUES ( Table[Code] ), [1st 3_M WoUsg] )

    • George1973's avatar
      George1973
      Helper V
      4 years ago

      tamerj1 ,
      Unfotunately the results with the newest (simle) formula got worse (Differences increase)

       

      Month Usage Sum = SUMX ( VALUES (C_GOODS[პროდუქციის კოდი]), [1st 3_M WoUsg] )

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Can you place both measures in a separate matix along with the codes in the rows