Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nicoenz
Helper II
Helper II

how to sumx based on product hiearchy?

Hi,

I have a table with 3 columns

 

BrandSKUSales
Aasdf50
Aqwer60
Azxcv80

B

asdf

70

B

zxcv65

C

zxcv33

C

qwer19

 

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
1 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. 

View solution in original post

4 REPLIES 4
FreemanZ
Super User
Super User

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

 

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

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
Super User
Super User

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 !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors