GROUPBY with ALLEXCEPT filter in measure

[DevDelwyn]

Is there a way to use ALLEXCEPT in filtering the input to a GROUPBY Table measure?

I am creating a dashboard to show comparitive figures between the latest report month and selected month that the user selects via slicer and can subsiquently be sliced by Cycle. The data shows Orders and Order Values but duplicates both of these fields as also contains Contacts for each order.

Order No	Order Value	Canc. Date Fix	Cycle No. Band	Contact Date	Contact Description
708456	8.06	04/06/2019	1-3	01/06/2019	AB1
708456	8.06	04/06/2019	1-3	03/06/2019	AB2
849576	4.5	05/04/2019	4-7	20/03/2019	AB5
519543	5.1	19/05/2019	<1	19/05/2019	AB1
8416791	8.1	20/05/2019	1-3	15/05/2019	AB4

I have successfully created a measure to show the Distinct No. of Orders Cancelled in the latest month as follows.

Latest Month Cancellations = 
CALCULATE(
    DISTINCTCOUNT('All Clients'[Order No.]),
    filter(
        ALLEXCEPT(
            'All Clients',
            'All Clients'[Cycle No. Band],
            ),
        format(('All Clients'[Canc. Date Fix]),"MMM")=[Latest Report Month]
        ) 
    )

However when applying this logic to a GroupBy so that the Order Value can also be sliced, the result is always the whole Month Value for latest month and will not pickup the cycle No. slicer. - It is as though it is treating the ALLEXCEPT as an ALL function. code attempt as follows

Latest Month Order Value Fix = var OrdValTBL2 = 
GROUPBY(
    FILTER(
        ALLEXCEPT(
            'All Clients',
            'All Clients'[Cycle No. Band],
            ),
        format('All Clients'[Canc. Date Fix],"MMM")=[Latest Report Month])
        ,
    'All Clients'[Order No.],
    "Min Ord Val",
    minx(CURRENTGROUP(),[Order Value])
    )
return 
sumx(OrdValTBL2,[Min Ord Val])

I don't know if I am asking the impossible or if my approach is incorrect but any assistance would be greatly received.

NB

The Groupby approach does produce correct figures without the Latest Month filter issue...

Order Value Fix = var OrdValTBL = 
GROUPBY(
   'All Clients',
   'All Clients'[Order No.],
   "Min Ord Val",
   minx(CURRENTGROUP(),[Order Value])
)
return 
sumx(OrdValTBL,[Min Ord Val])

[DouweMeer]

If you're trying to show the data from previous month, try:

calculate ( [expression] , previousmonth ( [dates] ) )

Much much easier.

[DevDelwyn]

Hi DouweMeer,

That will not give me the correct results as the data contains duplicates i.e. in the example data above order 708456 is listed twice with its order value of 8.06 - so your suggestion would calculate its order value as 16.12. 

The Groupby with ...

minx(CURRENTGROUP(),[Order Value]) 

..gives me the distinct order value for each order but I am not able to slice further in its current form.

[DouweMeer]

If you're looking only for the distinct PO numbers as of previous month, you could do something like 

VAR a1 = distinct ( selectcolumn ( 'table' , "column" , 'table'[column] ) 

to create your own table reference with distinct values. Then use that as a table to make your calculations on it.

RETURN

countx ( a1 , [column] ) 

There are multiple expressions that allow you to use a table reference instead of an actual table as context. You can even get string values from such a table if you ever need it. 

[DevDelwyn]

this is a good alternative to the working measure above for distinct PO numbers but this is not the solution to the stated problem. Perhaps my original explenation was not clear?

I am trying to calculate the sum of order values for distinct order no.s for a filtered month.

The filtered month needs to remain regardless of slicer selections by users (as it is to compare against various months) so my understanding is that it must use some sort of ALL function otherwise the value will be affected by slicers.

[DouweMeer]

How about use create a table with 

Date - Value

Jan      Stuff

Feb     Stuff

Where Value equals:

Value = 

VAR a1 = distinct ( selectcolumn ( 'table' , "PO" , 'table'[Order No] , "PO value" , 'table'[Order Value] ) 

RETURN

sumx ( a1 , [PO value] )

For previous month, just do:

calculate ( [Value] , previousmonth ( 'table'[Canc. Date Fix] )

Should do the trick I think.

[DevDelwyn]

Thank you DouweMeer for your patience & suggestions but I am confused by what you mean by 'create a table'? -  Also I feel that it is not quite addressing my particular issue.

I am trying to create a measure that is fixed to the latest month so that the user can select other (various) months by slicer which will show variance against this measure, I would also like the slicer on other fields to pass through it.

My original formula is calculating the correct value for the latest month, but is not changing when other slicers are amended (i.e. [Cycle No. Band]).

What I would really like to understand is why the 'ALLEXCEPT' which states [Cycle No. Band] is acting soley as an 'ALL' function when filtering the input to the GROUPBY table? (ie it gives the whole month total regardless of which band is selected?).

Latest Month Order Value Fix = var OrdValTBL2 = 
GROUPBY(
    FILTER(
        ALLEXCEPT(
            'All Clients',
            'All Clients'[Cycle No. Band],
            ),
        format('All Clients'[Canc. Date Fix],"MMM")=[Latest Report Month])
        ,
    'All Clients'[Order No.],
    "Min Ord Val",
    minx(CURRENTGROUP(),[Order Value])
    )
return 
sumx(OrdValTBL2,[Min Ord Val])

[v-piga-msft]

Hi @DevDelwyn ,

What about changing ALLEXCEPT to ALLSELETED ?

In addition, if it is convenient, could you share the dummy pbix file or function of [Latest Report Month] so that I could have a test on it?

Best  Regards,

Cherry

[DevDelwyn]

Hi @v-piga-msft ,

I cannot get ALLSELECTED to respond correctly, either it has no affect on the Latest Month Order Value Fix2 or responds with Multiple Arguments not allowed error. (...although I thought this function was for ignoring filters applied within the measure as opposed to from external slicers?)

[Latest Report Month] Measure is simply to get the name of the most recent month of Data.

Latest Report Month = format(CALCULATE(max('All Clients'[Canc. Date Fix]),all('All Clients')),"MMM")

I have also attached a sample copy of the .pbix file

link to pbix file

As stated, the 'Latest Month Order Value 2' measure produces the correct total Month value, but this does not change when slicers select a different Cycle No. Band.