Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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])
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.
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 @Anonymous ,
it returned "COUNTX(__table,[_value])" = 944 which is the distinct count of CTNR_NUM,
what is this imply?
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.
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
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.
@Anonymous
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.
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.
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?
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
@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])
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |