Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AshDil
Helper V
Helper V

Want to show different Sales at Category and Subcategory level

Hi,

 

I have 2 tables.

Table - 1:

DATECATEGORYSUB CATEGORYTYPESUB TYPESALES
1-Jan-23FURNITURETABLESWOODA5000
1-Jan-23FURNITURETABLESWOODB2000
1-Jan-23FURNITURETABLESWOODC3000
1-Jan-23FURNITURETABLESPLASTICD3000
1-Jan-23FURNITURETABLESPLASTICE4000
1-Jan-23FURNITURETABLESPLASTICF1000
1-Jan-23FURNITURECHAIRSWOODA8000
1-Jan-23FURNITURECHAIRSWOODB7000
1-Jan-23FURNITURECHAIRSWOODC4000
1-Jan-23FURNITURECHAIRSPLASTICDEFAULT2000
1-Jan-23FURNITURECHAIRSPLASTICD5000
1-Jan-23FURNITURECHAIRSPLASTICE3000
1-Jan-23FURNITURECHAIRSPLASTICF2000
1-Feb-23FURNITURETABLESWOODA5000
1-Feb-23FURNITURETABLESWOODB4000
1-Feb-23FURNITURETABLESWOODC3000
1-Feb-23FURNITURETABLESPLASTICD2000
1-Feb-23FURNITURETABLESPLASTICE6000
1-Feb-23FURNITURETABLESPLASTICF3000
1-Feb-23FURNITURECHAIRSWOODA6000
1-Feb-23FURNITURECHAIRSWOODB7000
1-Feb-23FURNITURECHAIRSWOODC3000
1-Feb-23FURNITURECHAIRSPLASTICD5000
1-Feb-23FURNITURECHAIRSPLASTICE4000
1-Feb-23FURNITURECHAIRSPLASTICF2000

Table -2:

DATECATEGORYSUB CATEGORYEXPECTED SALES
1-Jan-23FURNITURETABLE20000
1-Jan-23FURNITURECHAIRS40000
1-Feb-23FURNITURETABLE25000
1-Feb-23FURNITURECHAIRS35000

 

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

AshDil_0-1683889127179.png

Please help me to do it.

 

Thanks,

AshDil

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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.

vyanjiangmsft_0-1684220216487.png

Here is my solution.

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

vyanjiangmsft_1-1684220361492.png

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:

vyanjiangmsft_2-1684220647131.png

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

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.