Number of Core Products sold through assemblies

[MarcinChuchro]

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! 

[FBergamaschi]

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

[Fistachpl]

@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

            1

        ),

         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] 

[FBergamaschi]

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

[Fistachpl]

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

[FBergamaschi]

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

[Fistachpl]

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

[FBergamaschi]

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

[Fistachpl]

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

[FBergamaschi]

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

[FBergamaschi · Accepted solution]

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

[Fistachpl]

@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

[FBergamaschi]

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

FB

[Fistachpl]

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

[FBergamaschi]

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

[MarcinChuchro]

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]

[FBergamaschi]

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

[Fistachpl]

@FBergamaschi : here the link 

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