Sumif output based on Column not present in Visual

[Emmett52]

Hi,

Is it possible to create a dax measure which outputs the result of the SUM of SUMIFS of columns not present in a visual?
Example, suppose I have a table with Region, Country, Brand and Product as attribute columns along with Total Revenue and Volume.

I wish to calculate the sumifs for Region, Country, Brand even though Brand will be the only attribute column added to the visual  (in excel, this would be the SUMIFS([Revenue],[Region],[@[Region]],[Country],[@[Country]],[Brand],[@[Brand]])
(Basically, sales Mix calculation requirements)


Please try to ignore your first immediate question "why are you trying to do this?!! as above is a very simplified version of my needs which i will / should be able to scale to meet requirements.  

(also, of course each Country will only be assigned to 1 x region, but i'm only trying to understand if request is possible and so i'm keeping the data simple for my understanding of any suggested result!!)

I can currently achieve desired output using a lot (ALOT) of calculated columns (eg below), but the report is in no way dynamic.

Pack_NSRUC = 
VAR NSR = 
DIVIDE(
    CALCULATE(
                    SUM( DATA[NSR_Comp] ), 
                    FILTER( 
                        ALL( DATA ), 
                        DATA[Country] = SELECTEDVALUE( DATA[Country] ) 
                        && DATA[Channel] = SELECTEDVALUE( DATA[Channel])
                        && DATA[CPL] = SELECTEDVALUE( DATA[CPL])
                        && DATA[Brand] = SELECTEDVALUE( DATA[Brand])
                        && DATA[Brand Group] = SELECTEDVALUE( DATA[Brand Group])  ) ),
    CALCULATE(
                    SUM( DATA[Volume UC_Comp] ), 
                    FILTER( 
                        ALL( DATA ), 
                        DATA[Country] = SELECTEDVALUE( DATA[Country] ) 
                        && DATA[Channel] = SELECTEDVALUE( DATA[Channel])
                        && DATA[CPL] = SELECTEDVALUE( DATA[CPL])
                        && DATA[Brand] = SELECTEDVALUE( DATA[Brand])
                        && DATA[Brand Group] = SELECTEDVALUE( DATA[Brand Group])) ))

RETURN NSR

Thank you in advance for any suggestions or help!

[Emmett52]

Hey @lbendlin ,  the issue is that i'm trying to establish if it is possible to write a dax measure which will aggregate values based on a column not present in a visual,  im aware of 'virtual tables' but don't know much.

I am amble to achieve result in excel only as a result of 2 x pivot tables.
Pivot table 1 contains sales and volume by customer and material attributes (i.e line level detail), and to the right of pvt 1 is a bunch of sumifs based on those attributes.

Pivot table 2 pivots all of pivot 1 + the adjacent formulas, and so I am able to get sumif result (by way of vba to refresh pivot cache) for a single attribute on the basis that all prior hiercarchial attributes are present.

Basically, is it possible to write a measure which will give a sumifs output to a visual based on a column not present in the visual? 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

You can modify the filter context given by the choice of columns in a visual through a variety of operations in your measure.  KEEPFILTERS, REMOVEFILTERS, ALLSELECTED etc. 

Don't use SELECTEDVALUE inside CALCULATE - that will not be effective.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...