Forum Discussion
Solved
New Column Creation Help
My data is structured like so: In the Data column I have different labels such as PV, Number of Cards, etc. I would like to create a new data field titled: Spend per Card where it...
you can try to create a measure for this
Measure = DIVIDE(sumx(FILTER('Table','Table'[Data]="PV"),'Table'[Number(USD)]),sumx(FILTER('Table','Table'[Data]="Number of Cards"),'Table'[Number(USD)]))for JPY you can create a similar measure to get the speedpls see the attachment below
your screenshot is too small. could you pls paste your sample data here and also provide the expected output?
ryan_mayu Here is some sample data:
Table I have
| Account | Time | Number (JPY) | Data |
| 123 | Q1 | 50 | PV |
| 123 | Q2 | 60 | PV |
| 123 | Q1 | 10 | Number of Cards |
| 123 | Q2 | 20 | Number of Cards |
Table I want:
| Account | Time | Number (JPY) | Data |
| 123 | Q1 | 50 | PV |
| 123 | Q2 | 60 | PV |
| 123 | Q1 | 10 | Number of Cards |
| 123 | Q2 | 20 | Number of Cards |
| 123 | Q1 | 5 (50/10) | Spend per card |
| 123 | Q2 | 3 (60/20) | Spend per card |
in PQ,
1. select the Data column and pivot column
2. create speed column
3. select the last 3 columns and unpivot column
4. renname column name
pls see the attachment below
When I pivot column it looks like this and i think this is the problem:
Account Time PV Number of Cards 123 Q1 50 null 123 Q2 60 null 123 Q1 null 10 123 Q2 null 20 ryan_mayu FYI my full table looks something like this:
Account Time Product Number (USD) Number (JPY) Data Sub-Data Volume Sub-Volume 123 Q1 Credit 5 50 PV AFT International NA 123 Q2 Credit 6 60 PV Without AFT Domestic On-Us 123 Q2 Credit 4 40 PV Without AFT Domestic Off-Us 123 Q1 Credit 1 10 Number of Cards NA NA NA 123 Q2 Debit 2 20 Number of Cards NA NA NA I want spend per card to be total pv/number of cards - i am planning to create a slicer based on account and product and show a graph of spend per card according to the slicer