how to sumx based on product hiearchy?

[nicoenz]

Hi,

I have a table with 3 columns

Brand	SKU	Sales
A	asdf	50
A	qwer	60
A	zxcv	80
B	asdf	70
B	zxcv	65
C	zxcv	33
C	qwer	19

I also have a measure:

Sales = SUMX(ALL(Table[SKU]),[Sales])

I would like to be able to select whether to sum sales based on SKU or Brand but don't seem to find a way to do it within the same measure.  I know I can create an additional measure but i have dozens of them so duplication is not what i'm looking for.

So, basically it should be something like:

Sales = sumx ( all (  "the value i select from a switch should determine which column goes here"),[Sales])

Can anyone give me a hand on this? i'm lost

thanks,

nico

[FreemanZ]

This is standard use case for field parameter, a feature introduced not long. Enjoy.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[nicoenz]

thanks @FreemanZ @serpiva64 

but how do I make the measure to use the parameter i select?

When I use this measure it returns a 0

Business Lost SwitchFields = 

SUMX(ALL(Parameter[parameter fields]),(

    IF(and([Quantity P2]=0,[Quantity P1]>0), [CM P2]-[CM P1] , 0))

)

Quantity and CM are other measures that i use to filter which fields i want to sumx

[FreemanZ · Accepted solution]

Aha, you might need to use it in a different way: 

1) Try to create two measures: one for SKU and one for Brand. 

2) During field parameter creation, add those two measures to "Add and reorder fields".

3) A slicer shall be created automatically. 

4) If you use the field in the parameter table to plot a  visual. The visual shall change accordingly reflecting the selections on the slicer. 

It is a bit different from numeric parameter, where you get a variable out of parameter selection and you can refer that variable in DAX codes. 

[serpiva64]

Hi, 

in this case probably is possible to use Parameter Fields. 

You can see how to do in the video.

https://clipchamp.com/watch/I33pntSLD8G

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !