Want to show different Sales at Category and Subcategory level

[AshDil]

Hi,

I have 2 tables.

Table - 1:

DATE	CATEGORY	SUB CATEGORY	TYPE	SUB TYPE	SALES
1-Jan-23	FURNITURE	TABLES	WOOD	A	5000
1-Jan-23	FURNITURE	TABLES	WOOD	B	2000
1-Jan-23	FURNITURE	TABLES	WOOD	C	3000
1-Jan-23	FURNITURE	TABLES	PLASTIC	D	3000
1-Jan-23	FURNITURE	TABLES	PLASTIC	E	4000
1-Jan-23	FURNITURE	TABLES	PLASTIC	F	1000
1-Jan-23	FURNITURE	CHAIRS	WOOD	A	8000
1-Jan-23	FURNITURE	CHAIRS	WOOD	B	7000
1-Jan-23	FURNITURE	CHAIRS	WOOD	C	4000
1-Jan-23	FURNITURE	CHAIRS	PLASTIC	DEFAULT	2000
1-Jan-23	FURNITURE	CHAIRS	PLASTIC	D	5000
1-Jan-23	FURNITURE	CHAIRS	PLASTIC	E	3000
1-Jan-23	FURNITURE	CHAIRS	PLASTIC	F	2000
1-Feb-23	FURNITURE	TABLES	WOOD	A	5000
1-Feb-23	FURNITURE	TABLES	WOOD	B	4000
1-Feb-23	FURNITURE	TABLES	WOOD	C	3000
1-Feb-23	FURNITURE	TABLES	PLASTIC	D	2000
1-Feb-23	FURNITURE	TABLES	PLASTIC	E	6000
1-Feb-23	FURNITURE	TABLES	PLASTIC	F	3000
1-Feb-23	FURNITURE	CHAIRS	WOOD	A	6000
1-Feb-23	FURNITURE	CHAIRS	WOOD	B	7000
1-Feb-23	FURNITURE	CHAIRS	WOOD	C	3000
1-Feb-23	FURNITURE	CHAIRS	PLASTIC	D	5000
1-Feb-23	FURNITURE	CHAIRS	PLASTIC	E	4000
1-Feb-23	FURNITURE	CHAIRS	PLASTIC	F	2000

Table -2:

DATE	CATEGORY	SUB CATEGORY	EXPECTED SALES
1-Jan-23	FURNITURE	TABLE	20000
1-Jan-23	FURNITURE	CHAIRS	40000
1-Feb-23	FURNITURE	TABLE	25000
1-Feb-23	FURNITURE	CHAIRS	35000

In table-1, we have actual sales for different categories in each month. Table - 2, we have expected sales for different categories in each month.

Desired Output:

In matrix,

At subcategory level,

Under ACTUAL SALES column, wanted to show sum of all the tables and chairs sales monthwise by excluding subtype 'E' & 'F'

i.e., Actual Sales for Tables in Jan = Table Sales for Sub type A in Jan + Table Sales for Sub type B in Jan + Table Sales for Sub type C in Jan + Table Sales for Sub type D in Jan = 5000+2000+3000+ 3000 = 13000

Under Sales vs Expected Sales column, need to show "Actual Sales by excluding 'E' & 'F'  / Total Expected Sales" for both tables and charis

i.e., Sales Vs Expected Sales for tables in Jan = Actual Sales by excluding 'E' & 'F' in Jan / Total Expected Sales for Tables in Jan = 13000/20000 = 0.65 (In Percentage format: 65%)

At Category level,

Under ACTUAL SALES column, wanted to show sum of all the Furniture sales monthwise only for type = "WOOD"

i.e., Actual Sales for Furniture in Jan = Jan Table Sales for Type = "WOOD" + Jan Chair sales for Type = "WOOD" = (5000+2000+3000) + (8000+7000+4000) = 10000 + 19000 = 29000

Under Sales vs Expected Sales column, need to show "Actual Sales of Furniture for Type = "WOOD"/ Total expected Sales for Furniture"

i.e., Sales vs Expected Sales for Furniture in Jan = Actual Sales of Furniture for Type = "WOOD" in Jan / Total expected Sales for Furniture in Jan = 29000/60000 = 0.48 (In percentage format 48%)

We need to show the results as below

Please help me to do it.

Thanks,

AshDil

[v-yanjiang-msft]

Hi @AshDil ,

According to your description, I think we can't do it in one matrix. As in matrix, the value of "CATEGORY" must be the total of sub level "SUB CATEGORY", which is not consistent in your result.

Here is my solution.

1.Create a new table with one column like below.

2.Create two measures.

Measure =
VAR _SALES =
    SUMX (
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[DATE] = MAX ( 'Table1'[DATE] )
                && 'Table1'[TYPE] = "WOOD"
        ),
        'Table1'[SALES]
    )
VAR _EXP =
    SUMX (
        FILTER ( ALL ( 'Table2' ), 'Table2'[DATE] = MAX ( 'Table1'[DATE] ) ),
        'Table2'[EXPECTED SALES]
    )
RETURN
    IF (
        MAX ( 'Table'[SALES TYPE] ) = "SALES",
        _SALES,
        FORMAT ( DIVIDE ( _SALES, _EXP ), "#%" )
    )

Measure2 =
VAR _SALES =
    SUMX (
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[DATE] = MAX ( 'Table1'[DATE] )
                && 'Table1'[SUB CATEGORY] = MAX ( 'Table1'[SUB CATEGORY] )
                && NOT ( 'Table1'[SUB TYPE] IN { "E", "F" } )
        ),
        'Table1'[SALES]
    )
VAR _EXP =
    SUMX (
        FILTER (
            ALL ( 'Table2' ),
            'Table2'[DATE] = MAX ( 'Table1'[DATE] )
                && 'Table2'[SUB CATEGORY] = MAX ( 'Table1'[SUB CATEGORY] )
        ),
        'Table2'[EXPECTED SALES]
    )
RETURN
    IF (
        MAX ( 'Table'[SALES TYPE] ) = "SALES",
        _SALES,
        FORMAT ( DIVIDE ( _SALES, _EXP ), "#%" )
    )

Put Measure in first visual and Measure2 in second visual, get the result:

I attach my file below for your reference.

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

Best regards,

Community Support Team_yanjiang

[AshDil]

Hi @v-yanjiang-msft ,

Thanks for your solution. But the requirement is to show both of them in same matrix table only.

Please help me to achieve it.

Thanks,

AshDil

[v-yanjiang-msft]

Hi @AshDil ,

I'm afraid it can't be achieved, as I explained earlier, the value of "CATEGORY" must be the total of sub level "SUB CATEGORY" in the same matrix.

Best regards,

Community Support Team_yanjiang