cancel
Showing results for
Search instead for
Did you mean:  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.  1 ACCEPTED SOLUTION  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!

16 REPLIES 16  Community Support

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

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

phchu1226  Community Support

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

Hi @hp_2198 ,

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

what is this imply?   Community Support

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

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

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

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

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

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

Yes, you can split the matrix for companies A and B.  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.  Helper I

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?   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  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! ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks #### 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
Users online (4,342)