Sum Rows in Fact Table

[BogdanRak]

Hi,

Please, advise how to create measure and build Matrix (PivotTable)

I have fact table

Week_ID	Employee_ID	Product_ID	Calls	Distribution
1	1	NULL	10	NULL
2	1	NULL	10	NULL
3	1	NULL	10	NULL
1	1	1	NULL	5
1	1	2	NULL	7
1	1	3	NULL	3
2	1	1	NULL	7
2	1	2	NULL	8
2	1	3	NULL	9
3	1	1	NULL	5
3	1	2	NULL	5
3	1	3	NULL	5
1	2	NULL	20	NULL
2	2	NULL	20	NULL
3	2	NULL	20	NULL
1	2	1	NULL	5
1	2	2	NULL	7
1	2	3	NULL	3
2	2	1	NULL	7
2	2	2	NULL	8
2	2	3	NULL	9
3	2	1	NULL	5
3	2	2	NULL	5
3	2	3	NULL	5

And want to recieve such Pivot:

		Product
Week	Employee	1	2	3
1	1	SUM(Distribution)/SUM(Calls)
2	1
3	1
1	2
2	2
3	2

[Anonymous · Accepted solution]

Hi  @BogdanRak ,

Here are the steps you can follow：

1. Open the Matrix visual. – Row headers – options – stepped layout.

2. Create measure.

SUM(Distribution)/SUM(Calls) if divided by

0 becomes 0 whether it is divided by or divided by a number

Measure 2 =
var _1=
SUMX(FILTER(ALL('Table'),
'Table'[Week_ID]=MAX('Table'[Week_ID])&&'Table'[Employee_ID]=MAX('Table'[Employee_ID])&&'Table'[Product_ID]=
MAX('Table'[Product_ID])),[Distribution])
var _2=
SUMX(FILTER(ALL('Table'),
'Table'[Week_ID]=MAX('Table'[Week_ID])&&'Table'[Employee_ID]=MAX('Table'[Employee_ID])&&'Table'[Product_ID]=
MAX('Table'[Product_ID])),[Calls])
var _index1=
IF(
    _1=BLANK(),0,_1)
var _index2=
IF(
    _2=BLANK(),0,_2)
return
DIVIDE(
_index1,_index2)

SUM(Distribution)/SUM(Calls) if it is a concatenated string

Measure =
var _1=
SUMX(FILTER(ALL('Table'),
'Table'[Week_ID]=MAX('Table'[Week_ID])&&'Table'[Employee_ID]=MAX('Table'[Employee_ID])&&'Table'[Product_ID]=
MAX('Table'[Product_ID])),[Distribution])
var _2=
SUMX(FILTER(ALL('Table'),
'Table'[Week_ID]=MAX('Table'[Week_ID])&&'Table'[Employee_ID]=MAX('Table'[Employee_ID])&&'Table'[Product_ID]=
MAX('Table'[Product_ID])),[Calls])
var _index1=
IF(
    _1=BLANK(),0,_1)
var _index2=
IF(
    _2=BLANK(),0,_2)
return
_index1&"/"&_index2

If you need pbix, please click here.

Best Regards,

Liu Yang

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