cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erche3 Frequent Visitor
Frequent Visitor

create new table with Summarize

Hi ,

 

I am new at Power BI , and I am stuck at creating a new bridge table which is dynamic from other master table with filter.

 

I have a table ""structure"like below

 

bag# ,        item#     qty

Apple            A          1

Apple            B          2

Orange         A          3

 

I want to create a new table to list the unique value of " bag#" , and a sum qty , and it is dynamic when I filter by Item# from the "structure" table,

for example ,

if I filter item#= A ,I want to get below in new Table

Bag#       qty

Apple       1

Orange    3

 

I used : summarize(Structure, structure[bag#],"qty",caculate(sum(structure[qty]),allexcept(structure,structure[Item#])))

but it does not return correct qty .

 

anyone can help ,please ?

thanks a lot

 

Eric

 

4 REPLIES 4

Re: create new table with Summarize

Hi ,

 

You can import an attribute slicer from market place and drag Item into it. It will give the desired result.

image.png

 

 

You can remove Item too as below :

image.png

 

 

 

 

 

Super User
Super User

Re: create new table with Summarize

Hi @erche3,

 

Why do you want to make a summarize table? Depending on the visual you want you can create measure or add filters/Slicers to your reports that allow you to have the expectd quantites since the calculations are made based on context.

 

When you create a new table and considering you are using the summarize the resul of the full table will be the sum of all rows in this case 3 for each bag#.

 

You can use the summarize function to use in a measure that will then calculate the value you need.

 

Can you please add some addtional information about the expected result.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




erche3 Frequent Visitor
Frequent Visitor

Re: create new table with Summarize

thanks,but i need to use this table as bridge table to connect main data table ( with foc ,order stastistics in different period based on the bag#)
erche3 Frequent Visitor
Frequent Visitor

Re: create new table with Summarize

hi @MFelix thanks for ur help
in my case, i need this table to connect(create relationship) with another statistics data table on the bag level .since structure table has duplicate values on both bag# and fitting# ,i could not create relationship directly.
user has the need to search / filter on fitting# then get connnected[ bag#] [qty]. in the end statistics on fitting level by using bag statistics * [qty]

yes as you said , could i create a unique list.on bag# ( table = all (stucture[bag#]) and then create a new column to summarize the [qty] on the bag# from the filtered rows of stucture table .
so i can use to create one to many relationship with extra statistics table? and what is the correct formular ?or do you know other better way ?
thanks in beforehand

eric