SOLVED

DAX: SUM of ALLSELECTED by group (combine ALLSELECTED and ALLEXCEPT)

%3CLINGO-SUB%20id%3D%22lingo-sub-2739567%22%20slang%3D%22en-US%22%3EDAX%3A%20SUM%20of%20ALLSELECTED%20by%20group%20(combine%20ALLSELECTED%20and%20ALLEXCEPT)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2739567%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20sum%20the%20Qty%20by%20Quarter%2C%20taking%20into%20account%20the%20selection%20(eg.%20using%20slicers%20on%20Cat%20%26amp%3B%20Product).%20It's%20kind%20of%20a%20combination%20of%20ALLSELECTED%20and%20ALLEXCEPT.%3C%2FP%3E%3CP%3EI%20would%20have%20thought%20(cfr.%20also%20%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fa%2F59663726%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ethis%20solution%3C%2FA%3E)%20that%20the%20formula%20below%20would%20work%2C%20but%20it%20doesn't%3A%20it%20doesn't%20take%20Quarter%20into%20account%20and%20instead%20sums%20the%20whole%20Qty%20column.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCALCULATE(SUM(Table1%5BQty%5D)%3B%20ALLSELECTED(Table1)%3B%20VALUES(Table1%5BQuarter%5D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_0-1631273028646.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309575i5002D1DB2F482D01%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_0-1631273028646.png%22%20alt%3D%22bartvana_0-1631273028646.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2739567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741163%22%20slang%3D%22en-US%22%3ERe%3A%20DAX%3A%20SUM%20of%20ALLSELECTED%20by%20group%20(combine%20ALLSELECTED%20and%20ALLEXCEPT)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESuch%20formula%20is%20for%20the%20measure%2C%20not%20calculated%20column.%20In%20general%20it's%20better%20to%20avoid%20calculated%20columns%20at%20all%20and%20use%20only%20measures%2C%20with%20very%20few%20exceptions.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3ESum%20Selected%20by%20Quarter%3A%3DCALCULATE%20(%20%0A%20%20%20%20SUM%20(%20Table1%5BQty%5D%20)%2C%0A%20%20%20%20%20%20%20ALLSELECTED%20(%20Table1%20)%2C%0A%20%20%20%20%20%20%20VALUES%20(%20Table1%5BQuarter%5D%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI'm%20not%20sure%20what%20you'd%20like%20to%20calculate.%20What%20this%20measure%20do%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20599px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309662i4B2BB606B5C91B1A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFor%20the%20QuarterRank%20%3D%204%20we%20have%20values%20in%20quarters%201%20and%204%20(returned%20by%20ALLSELECTED).%20With%20that%20measure%20calculates%20sum%20of%20qty%20for%20all%20quarters%201%20and%204%2C%20now%20independently%20of%20the%20rank%2C%20totally%20116.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hello,

I am trying to sum the Qty by Quarter, taking into account the selection (eg. using slicers on Cat & Product). It's kind of a combination of ALLSELECTED and ALLEXCEPT.

I would have thought (cfr. also this solution) that the formula below would work, but it doesn't: it doesn't take Quarter into account and instead sums the whole Qty column.

Any help would be greatly appreciated!

=CALCULATE(SUM(Table1[Qty]); ALLSELECTED(Table1); VALUES(Table1[Quarter]))

bartvana_0-1631273028646.png

 

3 Replies
best response confirmed by bartvana (Frequent Contributor)
Solution

@bartvana 

Such formula is for the measure, not calculated column. In general it's better to avoid calculated columns at all and use only measures, with very few exceptions.

Sum Selected by Quarter:=CALCULATE ( 
    SUM ( Table1[Qty] ),
       ALLSELECTED ( Table1 ),
       VALUES ( Table1[Quarter] )
)

I'm not sure what you'd like to calculate. What this measure do

image.png

For the QuarterRank = 4 we have values in quarters 1 and 4 (returned by ALLSELECTED). With that measure calculates sum of qty for all quarters 1 and 4, now independently of the rank, totally 116.