[ Problems with sum on metric - removing blanks from the sum ]

[bolabuga]

Hello everyone,

I have the following metrics: 

CONSUME = SUM('sample'[number])

CONSUME AVERAGE = AVERAGEX(DATESINPERIOD('sample'[REFERENCIA];LASTDATE('sample'[REFERENCIA]);-6;MONTH);[CONSUME])

CONSUME FILTERED = IF([CONSUME AVERAGE]<=10 && [CONSUME]<15;[CONSUME];IF([CONSUME]<([CONSUME AVERAGE]*0,5);BLANK();IF([CONSUME]>([CONSUME AVERAGE]*1,5);BLANK();[CONSUME])))

CONSUME LY = CALCULATE([CONSUME FILTERED];SAMEPERIODLASTYEAR('sample'[REFERENCIA]))

CONSUME VAR = IF([CONSUME FILTERED]<>BLANK() && [CONSUME LY]<>BLANK();[CONSUME FILTERED]-[CONSUME LY];BLANK())

The matrix is looking like:

The intention here is having 1 month consume minus last year consume. applying the filters on the metrics i have pasted above.

The Matrix is showing the blanks on the right places, but the total values are reflecting all consumes. If i sum on excel, for example, i will have total consume in november 2016  smaller than "1651".

[v-ljerr-msft]

Hi @bolabuga,

Could you try using SUMX instead of SUM to calculate the CONSUME to see if it works?

CONSUME = SUMX('sample'[number])

Regards

[bolabuga]

@v-ljerr-msft

Thks for the reply.

the suggested syntax:  CONSUME = SUMX('sample'[number]) is not working. 

I can do SUMX('sample';sample'[number]), but its giving the exact same result of sum('sample'[number]).

If you are suggesting SUMX(filter('sample';sample'[number]<>blank());'sample'[number]), it also doesnt work. Because im creating blank spaces on the metrics "consume filtered", "consume ly" and "consume var".

[v-ljerr-msft]

Hi @bolabuga,

My mistake!

I believe the SUMX function is the key to solve this issue. However, without your table structures and sample data, I was not able to do a test. Could you try the formula below to see if it works this time.

CONSUME FILTERED =
SUMX (
    'TableNameWhereTheClientColumnIsIn';
    IF (
        [CONSUME AVERAGE] <= 10
            && [CONSUME] < 15;
        [CONSUME];
        IF (
            [CONSUME]
                < ( [CONSUME AVERAGE] * 0,5 );
            BLANK ();
            IF ( [CONSUME] > ( [CONSUME AVERAGE] * 1,5 ); BLANK (); [CONSUME] )
        )
    )
)

If it still doesn't work, could you post your table structures with some sample data? It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[bolabuga]

@v-ljerr-msft

Consume filter with SUMX didnt worked, the result is the same of a normal sum. I did a sample file trying to mimic the problem on the original file. 

On the sample we have too report views "case 1" and "case 2", both showing the same results, though the metrics on "case 1" view have some filters with the intent to remove specifics consumes from the final sum.

https://drive.google.com/open?id=0B-QUL9pXe3Yub2VtdnN1bTRKR1E (obs: theres a "little" download arrow on the upper right side of the screen accessing the link)

[Anonymous]

Hi @bolabuga

I have attached the details from the sample file for one client - a82603707m
Please let me know what is the output you want to see for this client.
What filters you will be using ?

The average is calculated for the six months , is it to be done always or based on the date filter.

Appreciate if you can throw some more light on your exact requirements to arrive at a solution.

Cheers

CheenuSing

[bolabuga]

@Anonymous

Hello cheenu, thks for you reply.

Let me try to share some more details.

Core idea: Have 1 specific list of clients and mount a matrix of those clients variation in consume over period of 12 months.

Example: The sample client related list being from nov/16, the final matrix should be showing results for range nov/16 till oct/17.

Premises:

- Average consume -> last 6 months

- Variation in consume -> one month consume subtracted from last year same period consume

- Consume this month should be filtered

          - Consume filtered -> all consume that average is <= 10 and consume <15 should be USED. Every consume thats

             [average*0,5] <= [consume] <= [average*1,5] shoud be USED

- Consume LYear month should be filtered

          - It should be used if its not Blank (we dont need LYear consume to be compared with it's average)

- The subtraction result should be blank if this month consume or the last year consume have no registered consume                        (blank()). obs: Zero is a valid consume.

Short version: I want the variation in consume, but only if consume is not to far +or- from the Average. Both consume and Lyear consumes must have valid(no blank) consumes to compare.

*** Consume is the "number" column in the sample file.

[bolabuga]

@Anonymous

Hello cheenu, thks for you reply.

Let me try to share some more details.

Core idea: Have 1 specific list of clients and mount a matrix of those clients variation in consume over period of 12 months.

Example: The sample client related list being from nov/16, the final matrix should be showing results for range nov/16 till oct/17.

Premises:

- Average consume -> last 6 months

- Variation in consume -> one month consume subtracted from last year same period consume

- Consume this month should be filtered

          - Consume filtered -> all consume that average is <= 10 and consume <15 should be USED. Every consume thats

             [average*0,5] <= [consume] <= [average*1,5] shoud be USED

- Consume LYear month should be filtered

          - It should be used if its not Blank (we dont need LYear consume to be compared with it's average)

- The subtraction result should be blank if this month consume or the last year consume have no registered consume                        (blank()). obs: Zero is a valid consume.

Short version: I want the variation in consume, but only if consume is not to far +or- from the Average. Both consume and Lyear consumes must have valid(no blank) consumes to compare.

*** Consume is the "number" column in the sample file.

[bolabuga]

@Anonymous

Hello cheenu, thks for you reply.

Let me try to share some more details.

Core idea: Have 1 specific list of clients and mount a matrix of those clients variation in consume over period of 12 months.

Example: The sample client related list being from nov/16, the final matrix should be showing results for range nov/16 till oct/17.

Premises:

- Average consume -> last 6 months

- Variation in consume -> one month consume subtracted from last year same period consume

- Consume this month should be filtered

          - Consume filtered -> all consume that average is <= 10 and consume <15 should be USED. Every consume thats

             [average*0,5] <= [consume] <= [average*1,5] shoud be USED

- Consume LYear month should be filtered

          - It should be used if its not Blank (we dont need LYear consume to be compared with it's average)

- The subtraction result should be blank if this month consume or the last year consume have no registered consume                        (blank()). obs: Zero is a valid consume.

Short version: I want the variation in consume, but only if consume is not to far +or- from the Average. Both consume and Lyear consumes must have valid(no blank) consumes to compare.

*** Consume is the "number" column in the sample file.

[bolabuga]

@v-ljerr-msft

Hummmmm...... This suggestion seens really good, i will test it as soon as i have a opening.

I will have to create a sample table to upload here, it will take me some more time, but if the suggestion is not working, i will do a sample and upload it here.

Thks again :).