Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |