Forum Discussion
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 calculates number of cards/pv for each row. How do you do this?
I wanted to follow these steps:
- Open Power Query Editor (Power BI: Home > Transform data).
- Identify columns: Ensure you have columns like Date, Data, and Value.
- Select the "Data" column.
- Go to Transform > Pivot Column.
- In the dialog:
Values Column = your numeric column (e.g., "Value").
Use default aggregation (Don’t Aggregate if available).
6. After pivoting, you'll get new columns like PV and Number of Cards.
7. Click Add Column > Custom Column and enter:
Spend per Card = [PV] / [Number of Cards]
8. Click Close & Apply.
But it did not work. I created Spend Per Card column like so but all values are null. (PV and Number of cards have values aside from null)
Any help on the above?
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
12 Replies
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
Hi 34583458hhdnsk ,
As I am not comfortable with Power Query , why don't you try DAX expression to do the same?
Hoping your data model has 2 measuresPV = CALCULATE( SUM( Table[Value] ), Table[Data] = "PV" )
NumberOfCards = CALCULATE( SUM( Table[Value] ), Table[Data] = "Number of Cards" )
Create a 3 measure:
Spend per Card =
DIVIDE(
[PV],
[NumberOfCards],
BLANK() // or 0
)Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
Thanks ryan_mayu maruthisp for your inputs, 34583458hhdnsk May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi 34583458hhdnsk
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank youHi 34583458hhdnsk
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
