Forum Discussion
Sumif output based on Column not present in Visual
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!
3 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523 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-Forum/ba-p/963216
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/1447523
