Calculate Fabrication per Product - DAX MEASURE

Question

I have this PBIX folder.

In this folder, you will find the PBIX File and the source data.

EDIT:

See herewith Sample Data as requested:

    TxDate | Reference | Code | ItemGroup | Quantity | Sales
__________________________________________________________________________
    24/02/2021 | AJI237677 | 2490/1008/999 |  | 1 | 342144.5
    28/02/2021 | AJI238993 | 1500/9999/999 |  | 1 | 140000
    13/04/2021 | AJI239912 | ATGS - Cut Pull Down | fabrication | 4 | 100
    13/04/2021 | AJI239912 | AC  760 200 15060  A | Alu-Ext-Std-Mil | 8 | 2512
    13/04/2021 | AJI239912 | AC  760 200 15060  A | Alu-Ext-Std-Mil | 6 | 1884
    13/04/2021 | AJI239916 | ATGS - Cut Guilotine | fabrication | 2 | 250
    13/04/2021 | AJI239917 | ATC252  SQR    60  A | Alu-Ext-Spe | 1 | 307
    13/04/2021 | AJI239917 | ATGH - 25MM3TA | Hardware | 8 | 256
    13/04/2021 | AJI239927 | ATGS - Cut Pull Down | fabrication | 1 | 0
    13/04/2021 | AJI239927 | AAE 127 127 16060  A | Alu-Ext-Std | 4 | 324
    13/04/2021 | AJI239929 | AHS 200 200 15060  A | Alu-Ext-Spe | 2 | 430
    13/04/2021 | AJI239929 | ATGS - Cut Pull Down | fabrication | 1 | 0
    13/04/2021 | AJI239933 | ATGH - 19MMSQCPC | Hardware | 4 | 56
    13/04/2021 | AJI239933 | AHS 200 200 15060  A | Alu-Ext-Spe | 1 | 215
    13/04/2021 | AJI239933 | AAU 500 250 16060  A | Alu-Ext-Std-Mil | 1 | 255
    13/04/2021 | AJI239947 | AXSTAIRNOSING | Alu-Ext-Spe | 3 | 915
    13/04/2021 | AJI239947 | ATGS - Cut Pull Down | fabrication | 1 | 0
    13/04/2021 | AJI239947 | ATGH - SEIBLACK | Hardware | 30 | 240
    13/04/2021 | AJI239950 | AS   202500125050    | Alu-Rol--She-Mil | 1 | 1240
    13/04/2021 | AJI239957 | ATGS - Cut Guilotine | fabrication | 7 | 175
    13/04/2021 | AJI239957 | AS   092500125050 P | Alu-Rol--She-Pre | 1 | 596
    13/04/2021 | AJI239966 | AC  444 190 16060  A | Alu-Ext-Std-Mil | 1 | 252

Using this Sample Data, I'm sure you'll be able to replicate it.

I need to be able to calculate the Fabrication Sales.

To explain this, each Product Item is sold to a Customer, but sometimes, fabrication of this item needs to take place, i.e. welding, bending, etc.

So for some invoices (Reference), the product is sold with fabrication.

The customer requires to see the Total Fabrication Sales per Item and the average percentage of Fabrication Sales, i.e. the percentage of the total invoice Fabrication takes up.

Using the following script in SQL, I'm able to replicate the required results:

with source as (
select
    Code
,   (select sum(ActualSalesValue) from _bvSTTransactionsFull t join _bvStockFull s on t.AccountLink = s.StockLink and ItemGroup = 'Fabrication' and tx.Reference = t.Reference and TxDate between '2020-03-01' and '2021-02-28') FabricationSales
,   (select sum(ActualSalesValue) from _bvSTTransactionsFull t join _bvStockFull s on t.AccountLink = s.StockLink and ItemGroup <> 'Fabrication' and tx.Reference = t.Reference and TxDate between '2020-03-01' and '2021-02-28') OtherSales
,   (select sum(ActualSalesValue) from _bvSTTransactionsFull t join _bvStockFull s on t.AccountLink = s.StockLink and tx.Reference = t.Reference and TxDate between '2020-03-01' and '2021-02-28') TotalSales
from    _bvSTTransactionsFull tx join _bvStockFull st on tx.AccountLink = st.StockLink
)
, results as (
select
    Code
,   isnull(round(sum(FabricationSales),2),0)    FabricationSales
,   isnull(round(sum(OtherSales),2),0)          OtherSales
,   isnull(round(sum(TotalSales),2),0)          TotalSales
from    source
group by
    Code
)

select
*
,   isnull(iif(isnull(TotalSales,0)=0,0,FabricationSales/TotalSales),0) [Fabrication%]
from    results
where   FabricationSales>0

The results look like this:

I need to replicate this using a DAX Formula.

I'm calculating the Sales using this measure : Sales = SUM( Sales[Sales] )

Then I'm filtering the sales by Item Group using this measure:

Fabrication Sales = 
CALCULATE( [Sales],
    FILTER( ProductGroups, ProductGroups[StGroup] = "Fabrication" )
)

I've tried the following measure to get my required results, but I just can't seem to get it right:

Actual Fabrication Sales = 
VAR InvoiceSales = SUMMARIZE( Sales, Sales[Reference], Products[Code], "InvSales", [Fabrication Sales] )
VAR TotalInvSales = SUMX( InvoiceSales, [InvSales] )
VAR ProductSales = SUMMARIZE( InvoiceSales, Products[Code], "ProductSales", TotalInvSales )
VAR Results = SUMX( ProductSales, [ProductSales] )
RETURN
Results

Please, if someone could help me with the correct DAX formula to get the required result?

If I could just get the correct DAX Formula to calculate the Fabrication Sales, I will be able to calculate the Quantity & Percentage.

EDIT:

Expected results as per @msta42a answer:

Can you add only sample data and desired results to your questions? I can't download your file because of internal policy. — msta42a
@msta42a, I've added additional sample data, please advise if sufficient? — Birel

msta42a msta42a · Accepted Answer · 2021-06-16T19:41:47

Ok, maybe I miss something but here we go. I split this into 3 measures: First, I search for a sum of sales for Fabrication ItemGroup in the scope of reference [In this sample = AJI239912]. Second, I search for all other ItemGroup in this scope. And at last, divide to get a percentage.

Fabrication Sales = 
CALCULATE( SUM(Sales[Sales]),
    FILTER( ALL(Sales[ItemGroup], Sales[Reference], Sales[Code]), Sales[ItemGroup] = "Fabrication"  && Sales[Reference] = SELECTEDVALUE(Sales[Reference]))
)

Other Sales = 
CALCULATE( SUM(Sales[Sales]),
    FILTER( ALL(Sales[ItemGroup], Sales[Reference], Sales[Code]), Sales[ItemGroup] <> "Fabrication"  && Sales[Reference] = SELECTEDVALUE(Sales[Reference]))
)

Fabrication% = DIVIDE([Fabrication Sales],[Other Sales],0)

Calculate Fabrication per Product - DAX MEASURE

1 Answers