Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PowerBI_newB23
Frequent Visitor

Matrix calculated measure not correct at lower level

‎04-17-2020 05:03 AM

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_DescSub_Product_DescSales MTD
Prod_1sub_prod_1586
Prod_1sub_prod_21
Prod_1sub_prod_344
Prod_1sub_prod_440
Prod_1sub_prod_55
Prod_1sub_prod_61
Prod_1sub_prod_71
Prod_1sub_prod_825
Prod_1sub_prod_92
Prod_1sub_prod_102
Prod_1sub_prod_113
Prod_1sub_prod_121
Prod_1sub_prod_1310
Prod_1sub_prod_14361
Total 697
Prod_2sub_prod_151
Prod_2sub_prod_1613
Prod_2sub_prod_17559
Prod_2sub_prod_1818
Prod_2sub_prod_19653
Prod_2sub_prod_203
Prod_2sub_prod_2134
Prod_2sub_prod_2235
Prod_2sub_prod_238869
  1268
   

 

@dax @NewB 

Message 1 of 8
975 Views
0
1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion
In response to PowerBI_newB23

‎04-17-2020 11:51 AM

Hi @PowerBI_newB23 ,

 

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/2020Prod_1Prod_14

 

Cat1.JPGCat2.JPGCat3.JPGCat4.JPG

 

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 🙂

View solution in original post

Message 8 of 8
872 Views
0
7 REPLIES 7
PowerBI_newB23
Frequent Visitor

‎04-17-2020 07:23 AM

 

@nandukrishnavs 

 

Sample Data Aggregated and Expected Output

Account_Open_DateProductSub_ProductNo_Accounts
4/1/2020Product_1Flexi VB 6
4/1/2020Product_1XBANK1 Current Account 86
4/1/2020Product_1XBANK1 Prosperity 6
4/1/2020Product_1XBANK1 Silver ST 5
4/1/2020Product_2XBANK1 Transact73
4/1/2020Product_2Flexi Save84
4/1/2020Product_2Mega U5
4/2/2020Product_1Flexi VB 3
4/2/2020Product_1XBANK1 Current Account 83
4/2/2020Product_1XBANK1 Silver ST 5
4/2/2020Product_1XBANK1 Prosperity 3
4/2/2020Product_2Islamic Savings1
4/2/2020Product_2Active Save3
4/2/2020Product_2XBANK1 Transact66
4/2/2020Product_2Flexi Save90
4/2/2020Product_2Mega U3
4/3/2020Product_1Flexi VB 2
4/3/2020Product_1XBANK1 Current Account 57
4/3/2020Product_1XBANK1 Silver ST 6
4/3/2020Product_1XBANK1 Prosperity 6
4/3/2020Product_1XBANK1 Islamic Cheque 1
4/3/2020Product_2Active Save3
4/3/2020Product_2XBANK1 Transact76
4/3/2020Product_2Flexi Save102
4/3/2020Product_2Mega U4
4/4/2020Product_1XBANK1 Current Account 11
4/4/2020Product_1Flexi VB 1
4/4/2020Product_1XBANK1 Silver ST 3
4/4/2020Product_2Flexi Save1
4/5/2020Product_1XBANK1 Current Account 5
4/5/2020Product_1XBANK1 Silver ST 1
4/5/2020Product_2Flexi Save1
4/6/2020Product_1XBANK1 Prosperity 3
4/6/2020Product_1XBANK1 Current Account 37
4/6/2020Product_1XBANK1 Silver ST 4
4/6/2020Product_2XBANK1 Transact57
4/6/2020Product_2Mega U6
4/6/2020Product_2Active Save1
4/6/2020Product_2Flexi Save53
4/7/2020Product_1Flexi VB 4
4/7/2020Product_1XBANK1 Silver ST 2
4/7/2020Product_1XBANK1 Current Account 52
4/7/2020Product_1XBANK1 Prosperity 6
4/7/2020Product_2Active Save5
4/7/2020Product_2Flexi Save81
4/7/2020Product_2XBANK1 Transact88
4/7/2020Product_2Mega U4
4/8/2020Product_1XBANK1 Silver ST 4
4/8/2020Product_1XBANK1 Current Account 57
4/8/2020Product_1Flexi VB 2
4/8/2020Product_1XBANK1 Prosperity 3
4/8/2020Product_1Estate Late Client 1
4/8/2020Product_2Flexi Save64
4/8/2020Product_2XBANK1 Transact56
4/8/2020Product_2Mega U3
4/9/2020Product_1XBANK1 Silver ST 3
4/9/2020Product_1XBANK1 Current Account 68
4/9/2020Product_1XBANK1 Prosperity 6
4/9/2020Product_1Flexi VB 3
4/9/2020Product_2Flexi Save90
4/9/2020Product_2Mega U5
4/9/2020Product_2Islamic Savings1
4/9/2020Product_2XBANK1 Transact62
4/9/2020Product_2Active Save3
4/10/2020Product_1XBANK1 Silver ST 2
4/10/2020Product_1XBANK1 Current Account 17
4/10/2020Product_1Flexi VB 1
4/11/2020Product_1XBANK1 Current Account 19
4/11/2020Product_2Flexi Save1
4/12/2020Product_1XBANK1 Current Account 6
4/13/2020Product_1XBANK1 Current Account 10
4/13/2020Product_2Flexi Save4
4/14/2020Product_1XBANK1 Silver ST 5
4/14/2020Product_1XBANK1 Current Account 80
4/14/2020Product_1Flexi VB 3
4/14/2020Product_1XBANK1 Prosperity 11
4/14/2020Product_2Mega U6
4/14/2020Product_2XBANK1 Transact104
4/14/2020Product_2Flexi Save107
4/14/2020Product_2Islamic Savings1
4/14/2020Product_2Active Save3
4/15/2020Product_1XBANK1 Prosperity 9
4/15/2020Product_1Flexi VB 3
4/15/2020Product_1XBANK1 Current Account 73
4/15/2020Product_1XBANK1 Silver ST 4
4/15/2020Product_1XBANK1 Islamic Cheque 1
4/15/2020Product_2Mega U6
4/15/2020Product_2Flexi Save74
4/15/2020Product_2XBANK1 Transact70
4/15/2020Product_2Islamic Savings1
4/15/2020Product_2Active Save1
4/16/2020Product_1XBANK1 Islamic Cheque 1
4/16/2020Product_1XBANK1 Silver ST 6
4/16/2020Product_1XBANK1 Current Account 66
4/16/2020Product_1XBANK1 Prosperity 9
4/16/2020Product_1Flexi VB 2
4/16/2020Product_1Estate Late Client 1
4/16/2020Product_2Mega U6
4/16/2020Product_2Flexi Save103
4/16/2020Product_2Active Save2
4/16/2020Product_2XBANK1 Transact87

 

 

Row LabelsSum of No_Accounts
Product_1874
Estate Late Client 2
Flexi VB 30
XBANK1 Current Account 727
XBANK1 Islamic Cheque 3
XBANK1 Prosperity 62
XBANK1 Silver ST 50
Product_21667
Active Save21
Flexi Save855
Islamic Savings4
Mega U48
XBANK1 Transact739
Grand Total2541
Message 5 of 8
924 Views
0
nandukrishnavs
Super User
Super User
In response to PowerBI_newB23

‎04-17-2020 07:56 AM

@PowerBI_newB23  - Try below DAX measure

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

 

mtd.JPG 


Regards,
Nandu Krishna

Message 6 of 8
919 Views
0
PowerBI_newB23
Frequent Visitor
In response to nandukrishnavs

‎04-17-2020 08:29 AM

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

Account_Open_DateProdSub_ProdNo_Accounts
3/19/2020Prod_1Prod_17
3/19/2020Prod_1Prod_229
3/19/2020Prod_1Prod_52
3/19/2020Prod_1Prod_617
3/19/2020Prod_1Prod_7636
3/19/2020Prod_1Prod_86
3/19/2020Prod_1Prod_9114
3/19/2020Prod_1Prod_1083
3/19/2020Prod_2Prod_1222
3/19/2020Prod_2Prod_13501
3/19/2020Prod_2Prod_142
3/19/2020Prod_2Prod_151
3/19/2020Prod_2Prod_16132
3/19/2020Prod_2Prod_17181
3/19/2020Prod_2Prod_181
3/19/2020Prod_2Prod_19507
3/20/2020Prod_1Prod_14
3/20/2020Prod_1Prod_236
3/20/2020Prod_1Prod_621
3/20/2020Prod_1Prod_7683
3/20/2020Prod_1Prod_87
3/20/2020Prod_1Prod_9127
3/20/2020Prod_1Prod_1071
3/20/2020Prod_2Prod_1230
3/20/2020Prod_2Prod_13491
3/20/2020Prod_2Prod_16116
3/20/2020Prod_2Prod_17248
3/20/2020Prod_2Prod_181
3/20/2020Prod_2Prod_19516
3/21/2020Prod_1Prod_717
3/21/2020Prod_1Prod_103
3/21/2020Prod_2Prod_17160
3/21/2020Prod_2Prod_1911
3/22/2020Prod_1Prod_23
3/22/2020Prod_1Prod_725
3/22/2020Prod_1Prod_91
3/22/2020Prod_1Prod_105
3/22/2020Prod_2Prod_141
3/22/2020Prod_2Prod_1776
3/22/2020Prod_2Prod_193
3/23/2020Prod_1Prod_14
3/23/2020Prod_1Prod_235
3/23/2020Prod_1Prod_41
3/23/2020Prod_1Prod_52
3/23/2020Prod_1Prod_611
3/23/2020Prod_1Prod_7637
3/23/2020Prod_1Prod_85
3/23/2020Prod_1Prod_998
3/23/2020Prod_1Prod_1085
3/23/2020Prod_2Prod_1229
3/23/2020Prod_2Prod_13596
3/23/2020Prod_2Prod_144
3/23/2020Prod_2Prod_152
3/23/2020Prod_2Prod_16134
3/23/2020Prod_2Prod_17288
3/23/2020Prod_2Prod_19552
3/24/2020Prod_1Prod_12
3/24/2020Prod_1Prod_230
3/24/2020Prod_1Prod_610
3/24/2020Prod_1Prod_7513
3/24/2020Prod_1Prod_88
3/24/2020Prod_1Prod_9100
3/24/2020Prod_1Prod_1057
3/24/2020Prod_1Prod_111
3/24/2020Prod_2Prod_1232
3/24/2020Prod_2Prod_13580
3/24/2020Prod_2Prod_144
3/24/2020Prod_2Prod_1689
3/24/2020Prod_2Prod_17209
3/24/2020Prod_2Prod_181
3/24/2020Prod_2Prod_19524
3/25/2020Prod_1Prod_12
3/25/2020Prod_1Prod_237
3/25/2020Prod_1Prod_612
3/25/2020Prod_1Prod_7633
3/25/2020Prod_1Prod_84
3/25/2020Prod_1Prod_981
3/25/2020Prod_1Prod_1063
3/25/2020Prod_2Prod_1241
3/25/2020Prod_2Prod_13587
3/25/2020Prod_2Prod_141
3/25/2020Prod_2Prod_151
3/25/2020Prod_2Prod_1668
3/25/2020Prod_2Prod_17291
3/25/2020Prod_2Prod_182
3/25/2020Prod_2Prod_19621
3/26/2020Prod_1Prod_11
3/26/2020Prod_1Prod_233
3/26/2020Prod_1Prod_615
3/26/2020Prod_1Prod_7415
3/26/2020Prod_1Prod_81
3/26/2020Prod_1Prod_928
3/26/2020Prod_1Prod_1050
3/26/2020Prod_2Prod_1223
3/26/2020Prod_2Prod_13543
3/26/2020Prod_2Prod_142
3/26/2020Prod_2Prod_1668
3/26/2020Prod_2Prod_17228
3/26/2020Prod_2Prod_181
3/26/2020Prod_2Prod_19543
3/27/2020Prod_1Prod_22
3/27/2020Prod_1Prod_745
3/27/2020Prod_1Prod_94
3/27/2020Prod_1Prod_106
3/27/2020Prod_2Prod_121
3/27/2020Prod_2Prod_1343
3/27/2020Prod_2Prod_1611
3/27/2020Prod_2Prod_1939
3/28/2020Prod_1Prod_22
3/28/2020Prod_1Prod_716
3/28/2020Prod_1Prod_103
3/28/2020Prod_2Prod_1319
3/28/2020Prod_2Prod_164
3/28/2020Prod_2Prod_1916
3/29/2020Prod_1Prod_21
3/29/2020Prod_1Prod_710
3/29/2020Prod_1Prod_103
3/29/2020Prod_2Prod_133
3/30/2020Prod_1Prod_24
3/30/2020Prod_1Prod_771
3/30/2020Prod_1Prod_81
3/30/2020Prod_1Prod_98
3/30/2020Prod_1Prod_109
3/30/2020Prod_2Prod_121
3/30/2020Prod_2Prod_1378
3/30/2020Prod_2Prod_141
3/30/2020Prod_2Prod_165
3/30/2020Prod_2Prod_1951
3/31/2020Prod_1Prod_27
3/31/2020Prod_1Prod_62
3/31/2020Prod_1Prod_774
3/31/2020Prod_1Prod_99
3/31/2020Prod_1Prod_106
3/31/2020Prod_2Prod_123
3/31/2020Prod_2Prod_1357
3/31/2020Prod_2Prod_163
3/31/2020Prod_2Prod_1943
4/1/2020Prod_1Prod_26
4/1/2020Prod_1Prod_786
4/1/2020Prod_1Prod_96
4/1/2020Prod_1Prod_105
4/1/2020Prod_2Prod_1384
4/1/2020Prod_2Prod_165
4/1/2020Prod_2Prod_1973
4/2/2020Prod_1Prod_23
4/2/2020Prod_1Prod_783
4/2/2020Prod_1Prod_93
4/2/2020Prod_1Prod_105
4/2/2020Prod_2Prod_123
4/2/2020Prod_2Prod_1390
4/2/2020Prod_2Prod_141
4/2/2020Prod_2Prod_163
4/2/2020Prod_2Prod_1966
4/3/2020Prod_1Prod_22
4/3/2020Prod_1Prod_757
4/3/2020Prod_1Prod_81
4/3/2020Prod_1Prod_96
4/3/2020Prod_1Prod_106
4/3/2020Prod_2Prod_123
4/3/2020Prod_2Prod_13102
4/3/2020Prod_2Prod_164
4/3/2020Prod_2Prod_1976
4/4/2020Prod_1Prod_21
4/4/2020Prod_1Prod_711
4/4/2020Prod_1Prod_103
4/4/2020Prod_2Prod_131
4/5/2020Prod_1Prod_75
4/5/2020Prod_1Prod_101
4/5/2020Prod_2Prod_131
4/6/2020Prod_1Prod_737
4/6/2020Prod_1Prod_93
4/6/2020Prod_1Prod_104
4/6/2020Prod_2Prod_121
4/6/2020Prod_2Prod_1353
4/6/2020Prod_2Prod_166
4/6/2020Prod_2Prod_1957
4/7/2020Prod_1Prod_24
4/7/2020Prod_1Prod_752
4/7/2020Prod_1Prod_96
4/7/2020Prod_1Prod_102
4/7/2020Prod_2Prod_125
4/7/2020Prod_2Prod_1381
4/7/2020Prod_2Prod_164
4/7/2020Prod_2Prod_1988
4/8/2020Prod_1Prod_11
4/8/2020Prod_1Prod_22
4/8/2020Prod_1Prod_757
4/8/2020Prod_1Prod_93
4/8/2020Prod_1Prod_104
4/8/2020Prod_2Prod_1364
4/8/2020Prod_2Prod_163
4/8/2020Prod_2Prod_1956
4/9/2020Prod_1Prod_23
4/9/2020Prod_1Prod_768
4/9/2020Prod_1Prod_96
4/9/2020Prod_1Prod_103
4/9/2020Prod_2Prod_123
4/9/2020Prod_2Prod_1390
4/9/2020Prod_2Prod_141
4/9/2020Prod_2Prod_165
4/9/2020Prod_2Prod_1962
4/10/2020Prod_1Prod_21
4/10/2020Prod_1Prod_717
4/10/2020Prod_1Prod_102
4/11/2020Prod_1Prod_719
4/11/2020Prod_2Prod_131
4/12/2020Prod_1Prod_76
4/13/2020Prod_1Prod_710
4/13/2020Prod_2Prod_134
4/14/2020Prod_1Prod_23
4/14/2020Prod_1Prod_780
4/14/2020Prod_1Prod_911
4/14/2020Prod_1Prod_105
4/14/2020Prod_2Prod_123
4/14/2020Prod_2Prod_13107
4/14/2020Prod_2Prod_141
4/14/2020Prod_2Prod_166
4/14/2020Prod_2Prod_19104
4/15/2020Prod_1Prod_23
4/15/2020Prod_1Prod_773
4/15/2020Prod_1Prod_81
4/15/2020Prod_1Prod_99
4/15/2020Prod_1Prod_104
4/15/2020Prod_2Prod_121
4/15/2020Prod_2Prod_1374
4/15/2020Prod_2Prod_141
4/15/2020Prod_2Prod_166
4/15/2020Prod_2Prod_1970
4/16/2020Prod_1Prod_11
4/16/2020Prod_1Prod_22
4/16/2020Prod_1Prod_766
4/16/2020Prod_1Prod_81
4/16/2020Prod_1Prod_99
4/16/2020Prod_1Prod_106
4/16/2020Prod_2Prod_122
4/16/2020Prod_2Prod_13103
4/16/2020Prod_2Prod_166
4/16/2020Prod_2Prod_1987
Message 7 of 8
900 Views
0
harshnathani
Community Champion
Community Champion
In response to PowerBI_newB23

‎04-17-2020 11:51 AM

Hi @PowerBI_newB23 ,

 

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/2020Prod_1Prod_14

 

Cat1.JPGCat2.JPGCat3.JPGCat4.JPG

 

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 🙂

Message 8 of 8
873 Views
0
Greg_Deckler
Super User
Super User

‎04-17-2020 05:12 AM

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-TIT...

 

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-...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 8
964 Views
0
PowerBI_newB23
Frequent Visitor
In response to Greg_Deckler

‎04-17-2020 05:40 AM

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_2sub_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. 

 

Message 3 of 8
958 Views
0
PowerBI_newB23
Frequent Visitor
In response to PowerBI_newB23

‎04-17-2020 06:04 AM

Can anyone help please? @Anonymous?

Message 4 of 8
946 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

View All