Forum Discussion
Data Modeling - Unpivot? Best Practice Needed
Dear Power BI Community,
Here is how my data is originally structured:
| Customer ID | Total ID's | Products |
| 1 | 8 | Product A, Product B, Product C |
| 2 | 5 | Product B, Product C |
| 3 | 6 | Product A |
| 4 | 9 | Product B, Product C |
| 5 | 4 | Product C |
I add 3 columns that look up the products:
| Customer ID | Total ID's | Products | Product A | Product B | Product C |
| 1 | 8 | Product A, Product B, Product C | 1 | 1 | 1 |
| 2 | 5 | Product B, Product C | 0 | 1 | 1 |
| 3 | 6 | Product A | 1 | 0 | 0 |
| 4 | 9 | Product B, Product C | 0 | 1 | 1 |
| 5 | 4 | Product C | 0 | 0 | 1 |
Then I pivot the Data Set:
| Company ID | Total ID's | Attribute | Value |
| 1 | 8 | Product A | 1 |
| 1 | 8 | Product B | 1 |
| 1 | 8 | Product C | 1 |
| 2 | 5 | Product A | 0 |
| 2 | 5 | Product B | 1 |
| 2 | 5 | Product C | 1 |
| 3 | 6 | Product A | 1 |
| 3 | 6 | Product B | 0 |
| 3 | 6 | Product C | 0 |
| 4 | 9 | Product A | 0 |
| 4 | 9 | Product B | 1 |
| 4 | 9 | Product C | 1 |
| 5 | 4 | Product A | 0 |
| 5 | 4 | Product B | 0 |
| 5 | 4 | Product C | 1 |
But now when I go to calculate "Total ID's" that number is obviously being triple counted.
I'm sure there is a better way to go about it but I am just not thinking about it in the right way.
Hi Anonymous ,
If you want to have the information at the Product level in my opinion you did the correct steps, however you need to take into account that the fields neeed to be handle in diferent ways when summarizing.
In your specific case the ID column needs to be summarized on DISTINCT COUNT and not on COUNT, because you will have triple calculation as you are getting.
Regards,
MFelix
5 Replies
Hi Anonymous ,
If you want to have the information at the Product level in my opinion you did the correct steps, however you need to take into account that the fields neeed to be handle in diferent ways when summarizing.
In your specific case the ID column needs to be summarized on DISTINCT COUNT and not on COUNT, because you will have triple calculation as you are getting.
Regards,
MFelix
That's good to know! So to sum up all the Total ID's, I was thinking a Dax Forumla something like:
Total ID's = calculate(sum(total ID's),Distinct(Company ID))
But that is still giving me the triple counting.
Hi Anonymous ,
You just need to use the DISTINCTCOUNT:
Total ID's = DISTINTCTCOUNT(Table[ID])Then depending on the context of the visual you are using you will get the filter by company, product, whatever other field you add to context.
Regards,
MFelix