Category Apportion from 5 Categories to 4 Categories Matrix

[arlequin71]

Hello,

I looking for an efficient / straight forward method to build a 4 Categories Matrix Table using the following rules from an original 5 categories Matrix.

notes:

There are several other columns not included in this example.

Segment and Sales belongs to 2 different tables 

- Segment E to be apportioned in new Matrix Segment A by 10 %

- Segment E to be apportioned in new Matrix Segment B by 70 %

- Segment E to be apportioned in New Matrix Segment C by 20 %

Thanks a lot in advance for your help.

[dax]

Hi arlequin71,

I assume that your sample might be similar to below

Create a column by expression in segment

Column =
CALCULATE (
    SUM ( SALE[AMOUNT] ),
    FILTER ( SALE, SALE[ID] <> RELATED ( SEGMENT[IDNAME] ) )
)

Then create two measures like below

Measure =
CALCULATE (
    SUM ( SALE[AMOUNT] ),
    FILTER ( SEGMENT, SEGMENT[IDNAME] = MIN ( SALE[ID] ) )
)
    + MIN ( SEGMENT[Column] ) * MIN ( SEGMENT[PERCENTAGE] )

all =
IF (
    HASONEVALUE ( SEGMENT[IDNAME] ),
    [Measure],
    SUMX ( ALLSELECTED ( SEGMENT ), [Measure] )
)

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[arlequin71]

Hello @dax , thanks for your answer.  Apparently i missed some important details... I don't have a Percentage Column at Segment Table and there is a customer hierarchy level which is grouped by Segment category.  The 5 Categories Matrix should be transformed in a 4 Categories Matrix apportioning the original E Segment over A, B, C and D Segment, so in the desired new Matrix :

A Segment Sales = A + 0.1 x E Segment Sales

B Segment Sales = B + 0.7 x E Segment Sales

C Segment Sales = C + 0.2 x E Segment Sales

D Segment Sales = D Segment Sales

Attached an example.

Thanks a lot in advance for your help.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZNJEsJADAP/MmcO2J6wHNlekcorgP9TkSWbk1VdWdTjZF2HjcN4fN+ffd7GdliHk3iRIIkik2Sfd5CFZKlrTiSYIGeSfb5ALiT7fIJcSa71HDuq4rHus6pt1cBUHIFM1a27m8ojkKl+hmQSsDYwKVg7mCQQyKSBkMzlgcCzlof/nX8toDfg8kBIN5eHt4fLw9vD5YHALvJAyJN3eSDwefLw9gh5IGSXkEf0PkIe8fcl1afU+wh5ZEgmj2iPkEe0R8gjeh8hj+h9hDwQst+UR4Zk8kDIc5nyQCCTB0K+Y9ZPMZvJA4H3ygOBTB4I/MfkgcDO8pi9j0UeCz22Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Customer = _t, Segment = _t])
in
    Source

[dax]

Hi

I want to know the sample you provide is sale table, right? Based on your sample, you might will link that table to sale fact table. I didn't create a fact table, I just create a column by range() function, I think the logic is similar(in your scenario, you could create relationship between two tables to calculate total.)

Then create table like below

Then you could follow my first reply to creaet column and measures, it will work

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arlequin71]

Hello @dax 

Sorry i think i'm still missing a step.  I modified my data model according to your instructions but i have an additional table (with Name and Percentage) that doesn't fit match the meassure you published.

Thanks again for your help.

This is what i have now:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLQM1SK1YlWcgKzzcFsZzDbCMx2AbGVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, percentage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"percentage", type number}})
in
    #"Changed Type"

[dax · Accepted solution]

Hi,

Did you create a column for your Table by expression?(In my sample, the table SALE is your table segment, and SEGMENT is your Table )

Columnseg = 
CALCULATE (
    [Total Sales],
    FILTER ( SALE, SALE[Segment] <> RELATED ( SEGMENT[name] ) )
)

Then you need to create two measures like below

Measure = 
CALCULATE (
    [Total Sales],
    FILTER ( SEGMENT, SEGMENT[name] = MIN ( SALE[Segment] ) )
)
    + MIN ( SEGMENT[Columnseg] ) * MIN ( SEGMENT[PERCENTAGE] )

all = 
IF (
    HASONEVALUE ( SEGMENT[name] ),
    [Measure],
    SUMX ( ALLSELECTED ( SEGMENT ), [Measure] )
)

Then use [all] in Matrix

Best Regards,
Zoe Zhi

[arlequin71]

Thanks a lot, it works!