topic Re: Number of Core Products sold through assemblies in DAX Commands and Tips

Number of Core Products sold through assemblies

MarcinChuchro — Sat, 04 Apr 2026 12:27:30 GMT

Hello,

I have a basic idea but I think I struggle with the syntax. So to explain my problem:

I have few tables:

1) dim Products and Assemblies which contains all core products and also all assemblies as I sell products and assemblies:

2) Assembly Configurations in which i say which core product in which amount is part of the assembly:

3) Sales Data in TSales:

Non in Visual Table I want to display how many times each core product was sold through an Assembly.

Example:

or order number ZO2 I sold Assembly A1 in the amount of 4 pieces. Assembly A1 consists 2 products 10 (Cookie) and 5 products s01 (salt) but I sold 4 of these assemblies. This means I sold: 4*2 = 8 cookies and 4*5 = 20 salt units.

My relationships:

I think in theory I know how to do it but as I mentioned I have a problem with the syntax. My idea is to:

_soldThroughAssemblies =
I go through each Catalogue No from dim Products and Assemblies where Type = "Product" - I VAR it as __coreProduct
For each Catalogue No I go through TSales and whenever I find Catalogue No where Type = "Assembly" (this i get from dim Products and Assemblies) I store it as __selectedAssembly I "open" the Assembly Configurations table

I need to find out how many (the Assemblyconfigurations[Amount]) __coreProducts are in __selectedAssembly and store it __Amount

then I calculate the __Amount by TSales[Amount] (TSales[Amount] gives me information how many of __selectedAssembly I sold) <- and I need to have a sum of it.

I kindly ask for Your support 🙂 Thank You!

Re: Number of Core Products sold through assemblies

FBergamaschi — Sat, 04 Apr 2026 14:34:18 GMT

Hi MarcinChuchro,

I offer you a simple code

CALCULATE (
SUM ( TSales[Amount] ),
'Assembly configuration'
)

I cannot say 100% this will work as you expect imeediately but looks a simple way to start

This will give you the sum of the Amount column of TSales only for Assemblies in the Assembly configuration table. I hope I interpreted correctky your request

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

MarcinChuchro — Sat, 04 Apr 2026 15:36:03 GMT

It only calculates how many times each assembly was sold.

What I need is to calculate how many times each core product was sold through assemblies.

I need it like this:
Go through dim Produts and Tables[Catalogue No]
For each product go through TSales
For current [Product or Assembly] check if in dim Products and Tables[Type] = "Assembly"

if so "open" this Assembly and find out the Assembly configurations[Amount] for the found assembly and current dim Produts and Tables[Catalogue No]

then multiply this amount by TSales[Amount]

Re: Number of Core Products sold through assemblies

FBergamaschi — Sat, 04 Apr 2026 16:49:48 GMT

Hi MarcinChuchro

can you share your pbix or the tables to reproduce your model? It is very difficult to code with images

So I can fix this

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Sat, 04 Apr 2026 16:55:44 GMT

FBergamaschi : here the link

https://1drv.ms/u/c/97de54f7273c5568/IQBH-lZ6CP7dQr6lVcouG92fAc6gladtdEv9o_N3fOY9ZAI

Re: Number of Core Products sold through assemblies

FBergamaschi — Sat, 04 Apr 2026 21:41:59 GMT

Hi again,

Test =

SUMX (

'Assembly configurations',

VAR Assembly = 'Assembly configurations'[Assembly cat no]

RETURN

CALCULATE (

SUM ( TSales[Amount] ),

TSales[Product or Assembly] = Assembly

) * 'Assembly configurations'[Amount]

)

File attached

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Sun, 05 Apr 2026 08:43:11 GMT

FBergamaschi we are almost there.

I need to use in table visual dim Products and Assemblies[Catalogue No] - so to start:

_m2 =

CALCULATE(

SUMX(

FILTER(

'dim Products and Assemblies',

'dim Products and Assemblies'[Type] = "Product"

VAR __p = 'dim Products and Assemblies'[Catalogue No]

RETURN

USERELATIONSHIP('dim Products and Assemblies'[Catalogue No],'Assembly configurations'[Product cat no])

)

I just need to syntax the code further - so now open TSales - for each assembly found there open assembly configurations and find out how may currently selected product there is in sold assembly (assembly configurations[Amount] when Assembly configurations[Product cat no] = currently selected product and multiply it by TSales[Amount]

Re: Number of Core Products sold through assemblies

FBergamaschi — Sun, 05 Apr 2026 09:17:40 GMT

Hello Fistachpl

the code works already if you put the column dim Products and Assemblies[Catalogue No] in rows in the visual, here my result from the file you already have (but I reattach it)

As you see you can put the columns in the visual as you prefer

Hope this solves

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Sun, 05 Apr 2026 09:33:38 GMT

But in Your file in visual You have: Assembly configurations[Catalogue No] not dim Producst and accessories[Catalogue no] if You use the one from dim table this is what You get:

and I need exctly this result:

but with Catalogue no from dim Producst and Acessories calculated for the whole sales table (to find out how many core products were sold within all sales of assemblies that contain these products

Re: Number of Core Products sold through assemblies

FBergamaschi — Sun, 05 Apr 2026 12:28:33 GMT

Hello Fistachpl

in the highlighted visuals I have dim Producst and accessories[Catalogue no] in rows

I attach again the results and the file

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Sun, 05 Apr 2026 12:48:47 GMT

FBergamaschi Hmm are You sure?

This is what You have:

For the first one You have:

dim Products and assemblies[Catalogue no] (which is table on the left), Assembly configurations[Product cat no] and test measure.

The problem is, that I need to have just dim Products and Assemblies[Catalogue no] and test measure because in table I would like to have:

dim Products and Assemblies[Catalogue No], measure: _soldDirectly, measure _soldThroughAssemblies (You named it test) and total sold which is just the sum of those two measures.

Right now I will have something like this:

In the column on the righ I should have these values:

So the total sold of product with dim Products and Assemblies[Catalogue no] = "10" should be 62 (sold diretly) + 84 (sold through assemblies) = 146 in total

Right now I will not be able to calculate the sum of soldDiretly and _soldThroughAssemblies.

I am sorry if I write something wrong/have problems explaining it. Thank You for Your involvement.

Re: Number of Core Products sold through assemblies

FBergamaschi — Sun, 05 Apr 2026 13:56:32 GMT

Hi Fistachpl

no problem at all. Only point is I do not have the _solddirectly measure, can you give me the code so i make it work to calculate the sum of both? Sorry if I have it somehwere but I cannot find it actually

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Sun, 05 Apr 2026 14:09:22 GMT

FBergamaschi I added it for a while but it is as simple as just:

_soldDirectly =

SUM(TSales[Amount])

and in the Visual I filter only for dim Products and Assemblies[Type] = "Product"

I did not save the file though.

Re: Number of Core Products sold through assemblies

FBergamaschi — Sun, 05 Apr 2026 15:39:09 GMT

Done

It was actually not simple 🙂

Now you can sum the two measures

File attached

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Sun, 05 Apr 2026 16:21:10 GMT

FBergamaschi thank You in advance. I cant see the uploaded file.

Re: Number of Core Products sold through assemblies

FBergamaschi — Sun, 05 Apr 2026 19:00:18 GMT

Hi Fistachpl,

my mistake. Now it is attached

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Number of Core Products sold through assemblies

Fistachpl — Tue, 07 Apr 2026 14:46:57 GMT

FBergamaschi

We are almost there but if I sum the items sold directy (Test sold direct) and sold through assemblies (Test) it is ok (blue rectangle) but the sum of turnover is a problem (red rectangle):

I assume the filter context is not 100% correct. Because the correct value is:

p.s. I not only need the measure but more I would like to understand how to get the correct value.

Updated file: Assembly calculations v3.pbix

Re: Number of Core Products sold through assemblies

FBergamaschi — Tue, 07 Apr 2026 14:53:45 GMT

Hello Fistachpl

that is another issue, in my opinion. The calculation of the items sold directly or through assemblies (the request you made in the original post) has been solved, is this correct? If the answer is yes, can you please mark is as a solution and kudo if you believe it makes sense to do this. Then, if you open another post, I can fix also that (and possibly other) measures (please include a link to this stream so that others can contribute if they want and @me so I can immediately help). Otherwise in my opinion we mix too many subjects in the same request.

Best