cancel
Showing results 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])

Proud to be a Super User!

16 REPLIES 16
Community Support

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

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

Best Regards,

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,

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,

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]))``````

Best Regards,

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,

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

Helper I

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?

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])

Proud to be a Super User!

Announcements