Forum Discussion
Solved
Article in 2 Columns linking two 1 Column
Hi community!
I have a master data table in this format:
| Article Type A | Article Type B | Attribute 1 |
| Bike_A | Bike_B | 5 |
| Chair_A | Chair_B | 10 |
Then I have a transactional table that contains both Article Types in 1 Column and the sales in 1 column
| Article | Type | Value |
| Bike_A | A | 50 |
| Bike_B | B | 50 |
| Chair_A | A | 50 |
Now I would like to create a table visual with that shows me in 1 column the Sales for Type A und 1 Column for Type B
| Article Type A | Article Type B |
| 100 | 50 |
How would you do that?
How would you build the data model and the measure?
Hi joshua1990 ,
the are two patterns how you can solve this (choose either one or the other).
- Use a matrix visual. Use column "Type" of your transactional table as columns in the matrix visual. Use column "Value" as values in the matrix visual, using the automatic aggregation "summarize". If you want to show the exact column names that you have given, you can add a calculated column to the transactional table and use this instead of the "Type" column in the matrix visual:
TypeLabel = "Article Type " & [Type] - You can use a table visual and create two measures "Article Type A" and "Article Type B" and add these two measures to the table visual:
Article Type A = CALCULATE(SUM('transactional table'[Value]),KEEPFILTERS('transactional table'[Type]="A"))
Article Type B = CALCULATE(SUM('transactional table'[Value]),KEEPFILTERS('transactional table'[Type]="B"))
Kind regards,
Martin
- Use a matrix visual. Use column "Type" of your transactional table as columns in the matrix visual. Use column "Value" as values in the matrix visual, using the automatic aggregation "summarize". If you want to show the exact column names that you have given, you can add a calculated column to the transactional table and use this instead of the "Type" column in the matrix visual:
1 Reply
Hi joshua1990 ,
the are two patterns how you can solve this (choose either one or the other).
- Use a matrix visual. Use column "Type" of your transactional table as columns in the matrix visual. Use column "Value" as values in the matrix visual, using the automatic aggregation "summarize". If you want to show the exact column names that you have given, you can add a calculated column to the transactional table and use this instead of the "Type" column in the matrix visual:
TypeLabel = "Article Type " & [Type] - You can use a table visual and create two measures "Article Type A" and "Article Type B" and add these two measures to the table visual:
Article Type A = CALCULATE(SUM('transactional table'[Value]),KEEPFILTERS('transactional table'[Type]="A"))
Article Type B = CALCULATE(SUM('transactional table'[Value]),KEEPFILTERS('transactional table'[Type]="B"))
Kind regards,
Martin
- Use a matrix visual. Use column "Type" of your transactional table as columns in the matrix visual. Use column "Value" as values in the matrix visual, using the automatic aggregation "summarize". If you want to show the exact column names that you have given, you can add a calculated column to the transactional table and use this instead of the "Type" column in the matrix visual: