topic Re: Matrix calculated measure not correct at lower level in DAX Commands and Tips

Matrix calculated measure not correct at lower level

Anonymous — Fri, 17 Apr 2020 12:03:04 GMT

Help please

I have a Matrix visual with two row fields. The calculated measure below only works when I have Product_Code_Desc but as soon as I add Sub_Product_Desc then the values are incorrect.

What I am looking for is for the measure below to be able to calculate at the second level i.e according to the Sub_Product_Desc. Currently it is including values that are not for the current month in question. The totals are however correct.

SALES MTD = CALCULATE(DISTINCTCOUNT(Data_Load[Account_Number]), DATESMTD(Data_Load[Account_Open_Date]),Data_Load[Account_Open_Date])

Product_Code_Desc	Sub_Product_Desc	Sales MTD
Prod_1	sub_prod_1	586
Prod_1	sub_prod_2	1
Prod_1	sub_prod_3	44
Prod_1	sub_prod_4	40
Prod_1	sub_prod_5	5
Prod_1	sub_prod_6	1
Prod_1	sub_prod_7	1
Prod_1	sub_prod_8	25
Prod_1	sub_prod_9	2
Prod_1	sub_prod_10	2
Prod_1	sub_prod_11	3
Prod_1	sub_prod_12	1
Prod_1	sub_prod_13	10
Prod_1	sub_prod_14	361
Total		697
Prod_2	sub_prod_15	1
Prod_2	sub_prod_16	13
Prod_2	sub_prod_17	559
Prod_2	sub_prod_18	18
Prod_2	sub_prod_19	653
Prod_2	sub_prod_20	3
Prod_2	sub_prod_21	34
Prod_2	sub_prod_22	35
Prod_2	sub_prod_23	8869
		1268

@dax @NewB

Re: Matrix calculated measure not correct at lower level

Greg_Deckler — Fri, 17 Apr 2020 12:12:38 GMT

So are both of those columns in the same table? Can you post any sample source data?

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Maybe Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Maybe MM3TR: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/td-p/411443

Re: Matrix calculated measure not correct at lower level

Anonymous — Fri, 17 Apr 2020 12:40:53 GMT

HI yes the fields are in the same table.

The time intelligence is not really something im after or is what the issue is. I'm looking to count all the accounts that have opened since the beginning of the current month. Which works when I have only one field under the rows section of the matrix. As soon as I add another (Sub_Product)- it includes records that should not be applicable to the calculation. For example

Prod_2

sub_prod_23

should not be included in the matrix result at all. The totals are correct as they are calculating at the Product Level. Unfortuantely I cannot share any sample data.

Re: Matrix calculated measure not correct at lower level

Anonymous — Fri, 17 Apr 2020 13:04:27 GMT

Can anyone help please? Anonymous?

Re: Matrix calculated measure not correct at lower level

Anonymous — Fri, 17 Apr 2020 14:23:11 GMT

nandukrishnavs

Sample Data Aggregated and Expected Output

Account_Open_Date	Product	Sub_Product	No_Accounts
4/1/2020	Product_1	Flexi VB	6
4/1/2020	Product_1	XBANK1 Current Account	86
4/1/2020	Product_1	XBANK1 Prosperity	6
4/1/2020	Product_1	XBANK1 Silver ST	5
4/1/2020	Product_2	XBANK1 Transact	73
4/1/2020	Product_2	Flexi Save	84
4/1/2020	Product_2	Mega U	5
4/2/2020	Product_1	Flexi VB	3
4/2/2020	Product_1	XBANK1 Current Account	83
4/2/2020	Product_1	XBANK1 Silver ST	5
4/2/2020	Product_1	XBANK1 Prosperity	3
4/2/2020	Product_2	Islamic Savings	1
4/2/2020	Product_2	Active Save	3
4/2/2020	Product_2	XBANK1 Transact	66
4/2/2020	Product_2	Flexi Save	90
4/2/2020	Product_2	Mega U	3
4/3/2020	Product_1	Flexi VB	2
4/3/2020	Product_1	XBANK1 Current Account	57
4/3/2020	Product_1	XBANK1 Silver ST	6
4/3/2020	Product_1	XBANK1 Prosperity	6
4/3/2020	Product_1	XBANK1 Islamic Cheque	1
4/3/2020	Product_2	Active Save	3
4/3/2020	Product_2	XBANK1 Transact	76
4/3/2020	Product_2	Flexi Save	102
4/3/2020	Product_2	Mega U	4
4/4/2020	Product_1	XBANK1 Current Account	11
4/4/2020	Product_1	Flexi VB	1
4/4/2020	Product_1	XBANK1 Silver ST	3
4/4/2020	Product_2	Flexi Save	1
4/5/2020	Product_1	XBANK1 Current Account	5
4/5/2020	Product_1	XBANK1 Silver ST	1
4/5/2020	Product_2	Flexi Save	1
4/6/2020	Product_1	XBANK1 Prosperity	3
4/6/2020	Product_1	XBANK1 Current Account	37
4/6/2020	Product_1	XBANK1 Silver ST	4
4/6/2020	Product_2	XBANK1 Transact	57
4/6/2020	Product_2	Mega U	6
4/6/2020	Product_2	Active Save	1
4/6/2020	Product_2	Flexi Save	53
4/7/2020	Product_1	Flexi VB	4
4/7/2020	Product_1	XBANK1 Silver ST	2
4/7/2020	Product_1	XBANK1 Current Account	52
4/7/2020	Product_1	XBANK1 Prosperity	6
4/7/2020	Product_2	Active Save	5
4/7/2020	Product_2	Flexi Save	81
4/7/2020	Product_2	XBANK1 Transact	88
4/7/2020	Product_2	Mega U	4
4/8/2020	Product_1	XBANK1 Silver ST	4
4/8/2020	Product_1	XBANK1 Current Account	57
4/8/2020	Product_1	Flexi VB	2
4/8/2020	Product_1	XBANK1 Prosperity	3
4/8/2020	Product_1	Estate Late Client	1
4/8/2020	Product_2	Flexi Save	64
4/8/2020	Product_2	XBANK1 Transact	56
4/8/2020	Product_2	Mega U	3
4/9/2020	Product_1	XBANK1 Silver ST	3
4/9/2020	Product_1	XBANK1 Current Account	68
4/9/2020	Product_1	XBANK1 Prosperity	6
4/9/2020	Product_1	Flexi VB	3
4/9/2020	Product_2	Flexi Save	90
4/9/2020	Product_2	Mega U	5
4/9/2020	Product_2	Islamic Savings	1
4/9/2020	Product_2	XBANK1 Transact	62
4/9/2020	Product_2	Active Save	3
4/10/2020	Product_1	XBANK1 Silver ST	2
4/10/2020	Product_1	XBANK1 Current Account	17
4/10/2020	Product_1	Flexi VB	1
4/11/2020	Product_1	XBANK1 Current Account	19
4/11/2020	Product_2	Flexi Save	1
4/12/2020	Product_1	XBANK1 Current Account	6
4/13/2020	Product_1	XBANK1 Current Account	10
4/13/2020	Product_2	Flexi Save	4
4/14/2020	Product_1	XBANK1 Silver ST	5
4/14/2020	Product_1	XBANK1 Current Account	80
4/14/2020	Product_1	Flexi VB	3
4/14/2020	Product_1	XBANK1 Prosperity	11
4/14/2020	Product_2	Mega U	6
4/14/2020	Product_2	XBANK1 Transact	104
4/14/2020	Product_2	Flexi Save	107
4/14/2020	Product_2	Islamic Savings	1
4/14/2020	Product_2	Active Save	3
4/15/2020	Product_1	XBANK1 Prosperity	9
4/15/2020	Product_1	Flexi VB	3
4/15/2020	Product_1	XBANK1 Current Account	73
4/15/2020	Product_1	XBANK1 Silver ST	4
4/15/2020	Product_1	XBANK1 Islamic Cheque	1
4/15/2020	Product_2	Mega U	6
4/15/2020	Product_2	Flexi Save	74
4/15/2020	Product_2	XBANK1 Transact	70
4/15/2020	Product_2	Islamic Savings	1
4/15/2020	Product_2	Active Save	1
4/16/2020	Product_1	XBANK1 Islamic Cheque	1
4/16/2020	Product_1	XBANK1 Silver ST	6
4/16/2020	Product_1	XBANK1 Current Account	66
4/16/2020	Product_1	XBANK1 Prosperity	9
4/16/2020	Product_1	Flexi VB	2
4/16/2020	Product_1	Estate Late Client	1
4/16/2020	Product_2	Mega U	6
4/16/2020	Product_2	Flexi Save	103
4/16/2020	Product_2	Active Save	2
4/16/2020	Product_2	XBANK1 Transact	87

Row Labels	Sum of No_Accounts
Product_1	874
Estate Late Client	2
Flexi VB	30
XBANK1 Current Account	727
XBANK1 Islamic Cheque	3
XBANK1 Prosperity	62
XBANK1 Silver ST	50
Product_2	1667
Active Save	21
Flexi Save	855
Islamic Savings	4
Mega U	48
XBANK1 Transact	739
Grand Total	2541

Re: Matrix calculated measure not correct at lower level

nandukrishnavs — Fri, 17 Apr 2020 14:56:31 GMT

Anonymous - Try below DAX measure

Total_MTD = CALCULATE(SUM('Table'[No_Accounts]),DATESMTD('Table'[Account_Open_Date]))

Re: Matrix calculated measure not correct at lower level

Anonymous — Fri, 17 Apr 2020 15:29:28 GMT

Thanks but thats my original formula. Try now with the extra month added in below. nandukrishnavs

Account_Open_Date	Prod	Sub_Prod	No_Accounts
3/19/2020	Prod_1	Prod_1	7
3/19/2020	Prod_1	Prod_2	29
3/19/2020	Prod_1	Prod_5	2
3/19/2020	Prod_1	Prod_6	17
3/19/2020	Prod_1	Prod_7	636
3/19/2020	Prod_1	Prod_8	6
3/19/2020	Prod_1	Prod_9	114
3/19/2020	Prod_1	Prod_10	83
3/19/2020	Prod_2	Prod_12	22
3/19/2020	Prod_2	Prod_13	501
3/19/2020	Prod_2	Prod_14	2
3/19/2020	Prod_2	Prod_15	1
3/19/2020	Prod_2	Prod_16	132
3/19/2020	Prod_2	Prod_17	181
3/19/2020	Prod_2	Prod_18	1
3/19/2020	Prod_2	Prod_19	507
3/20/2020	Prod_1	Prod_1	4
3/20/2020	Prod_1	Prod_2	36
3/20/2020	Prod_1	Prod_6	21
3/20/2020	Prod_1	Prod_7	683
3/20/2020	Prod_1	Prod_8	7
3/20/2020	Prod_1	Prod_9	127
3/20/2020	Prod_1	Prod_10	71
3/20/2020	Prod_2	Prod_12	30
3/20/2020	Prod_2	Prod_13	491
3/20/2020	Prod_2	Prod_16	116
3/20/2020	Prod_2	Prod_17	248
3/20/2020	Prod_2	Prod_18	1
3/20/2020	Prod_2	Prod_19	516
3/21/2020	Prod_1	Prod_7	17
3/21/2020	Prod_1	Prod_10	3
3/21/2020	Prod_2	Prod_17	160
3/21/2020	Prod_2	Prod_19	11
3/22/2020	Prod_1	Prod_2	3
3/22/2020	Prod_1	Prod_7	25
3/22/2020	Prod_1	Prod_9	1
3/22/2020	Prod_1	Prod_10	5
3/22/2020	Prod_2	Prod_14	1
3/22/2020	Prod_2	Prod_17	76
3/22/2020	Prod_2	Prod_19	3
3/23/2020	Prod_1	Prod_1	4
3/23/2020	Prod_1	Prod_2	35
3/23/2020	Prod_1	Prod_4	1
3/23/2020	Prod_1	Prod_5	2
3/23/2020	Prod_1	Prod_6	11
3/23/2020	Prod_1	Prod_7	637
3/23/2020	Prod_1	Prod_8	5
3/23/2020	Prod_1	Prod_9	98
3/23/2020	Prod_1	Prod_10	85
3/23/2020	Prod_2	Prod_12	29
3/23/2020	Prod_2	Prod_13	596
3/23/2020	Prod_2	Prod_14	4
3/23/2020	Prod_2	Prod_15	2
3/23/2020	Prod_2	Prod_16	134
3/23/2020	Prod_2	Prod_17	288
3/23/2020	Prod_2	Prod_19	552
3/24/2020	Prod_1	Prod_1	2
3/24/2020	Prod_1	Prod_2	30
3/24/2020	Prod_1	Prod_6	10
3/24/2020	Prod_1	Prod_7	513
3/24/2020	Prod_1	Prod_8	8
3/24/2020	Prod_1	Prod_9	100
3/24/2020	Prod_1	Prod_10	57
3/24/2020	Prod_1	Prod_11	1
3/24/2020	Prod_2	Prod_12	32
3/24/2020	Prod_2	Prod_13	580
3/24/2020	Prod_2	Prod_14	4
3/24/2020	Prod_2	Prod_16	89
3/24/2020	Prod_2	Prod_17	209
3/24/2020	Prod_2	Prod_18	1
3/24/2020	Prod_2	Prod_19	524
3/25/2020	Prod_1	Prod_1	2
3/25/2020	Prod_1	Prod_2	37
3/25/2020	Prod_1	Prod_6	12
3/25/2020	Prod_1	Prod_7	633
3/25/2020	Prod_1	Prod_8	4
3/25/2020	Prod_1	Prod_9	81
3/25/2020	Prod_1	Prod_10	63
3/25/2020	Prod_2	Prod_12	41
3/25/2020	Prod_2	Prod_13	587
3/25/2020	Prod_2	Prod_14	1
3/25/2020	Prod_2	Prod_15	1
3/25/2020	Prod_2	Prod_16	68
3/25/2020	Prod_2	Prod_17	291
3/25/2020	Prod_2	Prod_18	2
3/25/2020	Prod_2	Prod_19	621
3/26/2020	Prod_1	Prod_1	1
3/26/2020	Prod_1	Prod_2	33
3/26/2020	Prod_1	Prod_6	15
3/26/2020	Prod_1	Prod_7	415
3/26/2020	Prod_1	Prod_8	1
3/26/2020	Prod_1	Prod_9	28
3/26/2020	Prod_1	Prod_10	50
3/26/2020	Prod_2	Prod_12	23
3/26/2020	Prod_2	Prod_13	543
3/26/2020	Prod_2	Prod_14	2
3/26/2020	Prod_2	Prod_16	68
3/26/2020	Prod_2	Prod_17	228
3/26/2020	Prod_2	Prod_18	1
3/26/2020	Prod_2	Prod_19	543
3/27/2020	Prod_1	Prod_2	2
3/27/2020	Prod_1	Prod_7	45
3/27/2020	Prod_1	Prod_9	4
3/27/2020	Prod_1	Prod_10	6
3/27/2020	Prod_2	Prod_12	1
3/27/2020	Prod_2	Prod_13	43
3/27/2020	Prod_2	Prod_16	11
3/27/2020	Prod_2	Prod_19	39
3/28/2020	Prod_1	Prod_2	2
3/28/2020	Prod_1	Prod_7	16
3/28/2020	Prod_1	Prod_10	3
3/28/2020	Prod_2	Prod_13	19
3/28/2020	Prod_2	Prod_16	4
3/28/2020	Prod_2	Prod_19	16
3/29/2020	Prod_1	Prod_2	1
3/29/2020	Prod_1	Prod_7	10
3/29/2020	Prod_1	Prod_10	3
3/29/2020	Prod_2	Prod_13	3
3/30/2020	Prod_1	Prod_2	4
3/30/2020	Prod_1	Prod_7	71
3/30/2020	Prod_1	Prod_8	1
3/30/2020	Prod_1	Prod_9	8
3/30/2020	Prod_1	Prod_10	9
3/30/2020	Prod_2	Prod_12	1
3/30/2020	Prod_2	Prod_13	78
3/30/2020	Prod_2	Prod_14	1
3/30/2020	Prod_2	Prod_16	5
3/30/2020	Prod_2	Prod_19	51
3/31/2020	Prod_1	Prod_2	7
3/31/2020	Prod_1	Prod_6	2
3/31/2020	Prod_1	Prod_7	74
3/31/2020	Prod_1	Prod_9	9
3/31/2020	Prod_1	Prod_10	6
3/31/2020	Prod_2	Prod_12	3
3/31/2020	Prod_2	Prod_13	57
3/31/2020	Prod_2	Prod_16	3
3/31/2020	Prod_2	Prod_19	43
4/1/2020	Prod_1	Prod_2	6
4/1/2020	Prod_1	Prod_7	86
4/1/2020	Prod_1	Prod_9	6
4/1/2020	Prod_1	Prod_10	5
4/1/2020	Prod_2	Prod_13	84
4/1/2020	Prod_2	Prod_16	5
4/1/2020	Prod_2	Prod_19	73
4/2/2020	Prod_1	Prod_2	3
4/2/2020	Prod_1	Prod_7	83
4/2/2020	Prod_1	Prod_9	3
4/2/2020	Prod_1	Prod_10	5
4/2/2020	Prod_2	Prod_12	3
4/2/2020	Prod_2	Prod_13	90
4/2/2020	Prod_2	Prod_14	1
4/2/2020	Prod_2	Prod_16	3
4/2/2020	Prod_2	Prod_19	66
4/3/2020	Prod_1	Prod_2	2
4/3/2020	Prod_1	Prod_7	57
4/3/2020	Prod_1	Prod_8	1
4/3/2020	Prod_1	Prod_9	6
4/3/2020	Prod_1	Prod_10	6
4/3/2020	Prod_2	Prod_12	3
4/3/2020	Prod_2	Prod_13	102
4/3/2020	Prod_2	Prod_16	4
4/3/2020	Prod_2	Prod_19	76
4/4/2020	Prod_1	Prod_2	1
4/4/2020	Prod_1	Prod_7	11
4/4/2020	Prod_1	Prod_10	3
4/4/2020	Prod_2	Prod_13	1
4/5/2020	Prod_1	Prod_7	5
4/5/2020	Prod_1	Prod_10	1
4/5/2020	Prod_2	Prod_13	1
4/6/2020	Prod_1	Prod_7	37
4/6/2020	Prod_1	Prod_9	3
4/6/2020	Prod_1	Prod_10	4
4/6/2020	Prod_2	Prod_12	1
4/6/2020	Prod_2	Prod_13	53
4/6/2020	Prod_2	Prod_16	6
4/6/2020	Prod_2	Prod_19	57
4/7/2020	Prod_1	Prod_2	4
4/7/2020	Prod_1	Prod_7	52
4/7/2020	Prod_1	Prod_9	6
4/7/2020	Prod_1	Prod_10	2
4/7/2020	Prod_2	Prod_12	5
4/7/2020	Prod_2	Prod_13	81
4/7/2020	Prod_2	Prod_16	4
4/7/2020	Prod_2	Prod_19	88
4/8/2020	Prod_1	Prod_1	1
4/8/2020	Prod_1	Prod_2	2
4/8/2020	Prod_1	Prod_7	57
4/8/2020	Prod_1	Prod_9	3
4/8/2020	Prod_1	Prod_10	4
4/8/2020	Prod_2	Prod_13	64
4/8/2020	Prod_2	Prod_16	3
4/8/2020	Prod_2	Prod_19	56
4/9/2020	Prod_1	Prod_2	3
4/9/2020	Prod_1	Prod_7	68
4/9/2020	Prod_1	Prod_9	6
4/9/2020	Prod_1	Prod_10	3
4/9/2020	Prod_2	Prod_12	3
4/9/2020	Prod_2	Prod_13	90
4/9/2020	Prod_2	Prod_14	1
4/9/2020	Prod_2	Prod_16	5
4/9/2020	Prod_2	Prod_19	62
4/10/2020	Prod_1	Prod_2	1
4/10/2020	Prod_1	Prod_7	17
4/10/2020	Prod_1	Prod_10	2
4/11/2020	Prod_1	Prod_7	19
4/11/2020	Prod_2	Prod_13	1
4/12/2020	Prod_1	Prod_7	6
4/13/2020	Prod_1	Prod_7	10
4/13/2020	Prod_2	Prod_13	4
4/14/2020	Prod_1	Prod_2	3
4/14/2020	Prod_1	Prod_7	80
4/14/2020	Prod_1	Prod_9	11
4/14/2020	Prod_1	Prod_10	5
4/14/2020	Prod_2	Prod_12	3
4/14/2020	Prod_2	Prod_13	107
4/14/2020	Prod_2	Prod_14	1
4/14/2020	Prod_2	Prod_16	6
4/14/2020	Prod_2	Prod_19	104
4/15/2020	Prod_1	Prod_2	3
4/15/2020	Prod_1	Prod_7	73
4/15/2020	Prod_1	Prod_8	1
4/15/2020	Prod_1	Prod_9	9
4/15/2020	Prod_1	Prod_10	4
4/15/2020	Prod_2	Prod_12	1
4/15/2020	Prod_2	Prod_13	74
4/15/2020	Prod_2	Prod_14	1
4/15/2020	Prod_2	Prod_16	6
4/15/2020	Prod_2	Prod_19	70
4/16/2020	Prod_1	Prod_1	1
4/16/2020	Prod_1	Prod_2	2
4/16/2020	Prod_1	Prod_7	66
4/16/2020	Prod_1	Prod_8	1
4/16/2020	Prod_1	Prod_9	9
4/16/2020	Prod_1	Prod_10	6
4/16/2020	Prod_2	Prod_12	2
4/16/2020	Prod_2	Prod_13	103
4/16/2020	Prod_2	Prod_16	6
4/16/2020	Prod_2	Prod_19	87

Re: Matrix calculated measure not correct at lower level

Anonymous — Fri, 17 Apr 2020 18:51:53 GMT

Hi Anonymous ,

Please use the below measure:

Count of Products =

var __subproduct = CALCULATE(COUNT(Data11[No_Accounts]),ALLExcept(Data11,Data11[Account_Open_Date],Data11[Product],Data11[Sub_Product]))

var __product = CALCULATE(COUNT(Data11[No_Accounts]),ALLExcept(Data11,Data11[Account_Open_Date],Data11[Product]))

var __date = COUNT(Data11[No_Accounts])

var __subproductscope = ISINSCOPE(Data11[Sub_Product])

var __productscope = ISINSCOPE(Data11[Product])

return

switch(

true(),

__subproductscope,__subproduct,

__productscope,__product,

__date

)

I have used Count() as I was unsure whether you want a disctint count or sum. You can replace with your preferred functions.

Also, I added one extra column to validate my results.

3/19/2020

Prod_1

Do let me know if this solution works.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution!

Appreciate with a kudos 🙂