Summarization for Measure Does not Work

[George1973]

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 · Accepted solution]

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 ) )
    )

[tamerj1]

@George1973 

Try is simple by referring to old measure

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

[George1973]

@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]

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

[George1973]

Like that?

[tamerj1]

I mean copy the matrix and remove everything except the the old and the new measures

[George1973]

Code	1st 3_M WoUsg	Month Usage Sum
00273	1	1
00592	1
01269	1	1
01293	1	1
01311	3	3
01312	2	2
01314	3	3
01346	1
01558	2	2
01568	1	1
01569	1
01570	3	3
01571	3	3
01578	3	3
01580	2	2
01581	3	3
01583	2	2
01593	1	1
01843	2
01987	3	3
01996	1
02714	2	2
02716	1
02847	2	2
02982	2	2
03310	1	1
03358	1
03371	1
03372	3	3
03437	2	2
03515	1	1
03516	1
03517	1	1
03586	2	2
03771	3	3
03884	1	1
03892	1
03899	3	3
03950	3	3
03954	2	2
04048	1
04128	1
04508	3	3
04509	3	3
04534	1
04583	1	1
04790	3	3
04791	1	1
04830	3	3
04834	1	1
04964	1
04968	1	1
04969	1
04970	1	1
05099	3	3

[tamerj1]

@George1973 

It could be the variable that is blocking the contrxt transition. Try

1st 3_M WoUsg =
SUMX (
    VALUES ( Table[Code] ),
    CALCULATE (
        COUNTAX ( FMnCount3, [MTV] ),
        FILTER (
            UNION (
                ROW ( "MTV", [-1M] ),
                ROW ( "MTV", [-2M] ),
                ROW ( "MTV", [Sold Prod Qnty] )
            ),
            [MTV] > 0
        )
    )
)

[George1973]

@tamerj1 

:)))))))

Unfotunately, absolutely the same result and the same differences..

I'm going slightly mad 

[tamerj1]

I don't know why it behaved this way. Maybe we can connect tomorrow and find a solution. 

[tamerj1 · Accepted solution]

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]

@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]

Try wrap the formula with KEEPFILTERS

[George1973]

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 · Accepted solution]

@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]

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]

@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]

@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]

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

[George1973]

One hint,
What I found out is that, the differences are in the cases, where [Sold Prod Qnty] is blank.
Meaning, that:
1. All differnces happen with the blank [sold prod qnty] 
2. All blank [sold prod qnty] cases are not differences

[George1973]

Meybe it happens because the Product Codes are in TEXT format? (Just an Idea)