cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
phchu1226
Helper I
Helper I

About the subtotal and total in matrix

Hi,

I am creating a matrix for shipping container.

However, I found that the subtotal and total are incorrect and I think I understand the reason behind, I still can't figure out how to solve this after reading lots of sources on the internet...

For 'From Port', I have CompanyA , Company B

For 'CTNR_SIZE', I have Type A, Type B, Type C

this matrix is used to count the number of container we used each month.

The distinct count by type and month are alright but the subtotal and total not.

For example, company B, there is a CTNR_NUM, CTNR20200401 appears in both Type A and Type B in April. As the subtotal in April of company B is counting the distinct value, so it is 1 less than (116+75+36 = 227)

 I am not using any DAX, anyone can tell me what is the appropriate way to solve this issue in details?

Thank you so much.

phchu1226_0-1620893325003.pngphchu1226_1-1620893405355.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@phchu1226 , is it distinctcount from CTNR_NUM, if so try like

sumx(summarize(Table,Table[From Port], Table[CTNR_SIZE],"_1", calculate( distinctcount ([CTNR_NUM]))),[_1])

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

16 REPLIES 16
v-xulin-mstf
Community Support
Community Support

Hi @phchu1226

 

 You can verifies if the context is being sliced by one value.

Please try measure as:

Measure=
IF(HASONEVALUE([CTNR_NUM]),COUNT([CTNR_NUM]),BLANK())  

 

Best Regards,
Link

 

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

@v-xulin-mstf 

Hi, tried the measure above and it returns (Blank)..

phchu1226

Hi @phchu1226

 

You can try measure as:

Total 1 = 
VAR __table = SUMMARIZE('Table1',[CTNR_NUM],"_value",COUNT([CTNR_NUM]))
RETURN
IF(HASONEVALUE(Table1[CTNR_NUM]),COUNT([CTNR_NUM]),COUNTX(__table,[_value]))

 

Best Regards,
Link

 

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

 

Hi @hp_2198 ,

it returned "COUNTX(__table,[_value])" = 944 which is the distinct count of CTNR_NUM,

what is this imply?

phchu1226_0-1621237734473.png

 

Hi @phchu1226,

 

Does this measure return the correct total?
For issues where the total is not displayed as expected in the matrix, please refer:

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

 

Best Regards,
Link

 

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

Does this measure return the correct total?

> I believe no.. 944 is just like calculate(DISTINCTCOUNT([CTNR_NUM]))

 

In fact, there are some order number which repeated with different company, so thats why the total should be larger than 944.

I dont know how can I archieve this.

Hi @phchu1226,

 

Sorry, i have modified the measure as:

Total 1 = 
VAR __table = SUMMARIZE('Table1',[CTNR_NUM],"_value",COUNT([CTNR_NUM]))
RETURN
IF(HASONEVALUE(Table1[CTNR_NUM]),COUNT([CTNR_NUM]),SUMX(__table,[_value]))

Please check.

 

Best Regards,
Link

 

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

@v-xulin-mstf 

I dont know whether my question is not clear enough, let me explain it in details and hope you can understand😉

 

Now I have a table with columns[Date],[CTNR_NUM],[CTNR_SIZE],[COMPANY]

and I want to create a matrix for showing these data.

phchu1226_0-1621242043900.pngphchu1226_1-1621242138729.png

 

However, I have a trouble on the distinct count problem..

944 is the number of distinct count(CTNR_NUM) in this table,

I think the total number is not accurate enough as there are some [CTNR_NUM] is the same with different [CTNR_SIZE] (i.e. HK001 & 20'  , HK001 & 40'). In this case, some of these 'duplicate' records would not be counted in the subtotal and total but it counts in the values of rows and columns.. (for example: In April, China, the values are 116+75+36 = 227, it is not euqal to the subtotal 226.) I think 227 should be correct instead of 226..

I dont know how to make those subtotal and total compute correctly..

 

And I tried amitchandak approach to do so, the result is as below,

everything looks good, the total value, 966 ,seems right. 

However, the row of China & 40' have some problem.. I think some [CTNR_NUM] counted 2 more so that the subtotal and total are not match..phchu1226_3-1621243256638.png

Do you have any idea what's happen?

And do this approach is a good way to do so?

Sorry for the long message but I hope that I can solve this problem as it is quite important that we have a 100% accurate data to display.

Thank you so much.

 

phchu1226

 

Hi @phchu1226

 

Could you provide pbix file after removing sensitive data?

I will check it in my environment.

 

Best Regards,
Link

 

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

Hi @v-xulin-mstf ,

Thank you for your help.

Finally, I found out what's the problem..

I took amitchandak's dax but it missing a groupBy_columnName> which is the date, so the subtotal and total still have something wrong.

After added one more <groupBy_columnName> into summarize dax, the result is what I need.

the subtotal and total are going well.

phchu1226_0-1621313769280.png

 

phchu1226
Helper I
Helper I

@hp_2198

 

Hello, do you mean I can split this matrix into two matrix which correspond to different companies? It would avoid the miscalculation of subtotal and total?

or just keep this matrix, do not display subtotal and total there, then display them in another matrix?

Thank you for your reply!

 

phchu1226

Yes, you can split the matrix for companies A and B. 

hp_2198
Helper I
Helper I

hello!

you can create one more matrix to solve the problem for subtotal. here it goes how 

subtotal = calculate ([court of CTNR_NUM]),FILTER(table_name,FromPort = "company1")

In the filter add the details of the column name according to which you want your sum.

phchu1226
Helper I
Helper I

@amitchandak 

used your dax and put it into 'Values', everything go well except these two subtotal,

165+102+167+227+90 = 751

229+134+217+272+116 = 968

Do you know what's happen on these two subtotal?

phchu1226_1-1621226265261.png

 

 

phchu1226
Helper I
Helper I

Hi, @amitchandak

I am new of powerbi, and have lack of knowledge about power query. I am reading the doc from microsoft to understand what is the usage of those function. Can you slightly explain what is the logic of your power query? and what is the main difference between your power query and just drag and drop the count of CTNR_NUM from fields on right hand side?   Thank you very much!!  

phchu1226 

amitchandak
Super User IV
Super User IV

@phchu1226 , is it distinctcount from CTNR_NUM, if so try like

sumx(summarize(Table,Table[From Port], Table[CTNR_SIZE],"_1", calculate( distinctcount ([CTNR_NUM]))),[_1])

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors