New Column Creation Help

[34583458hhdnsk]

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:

1. Open Power Query Editor (Power BI: Home > Transform data).
2. Identify columns: Ensure you have columns like Date, Data, and Value.
3. Select the "Data" column.
4. Go to Transform > Pivot Column.
5. 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?

[v-priyankata]

Hi @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.

[v-priyankata]

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 you

[v-priyankata]

Hi @34583458hhdnsk 

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.

[maruthisp]

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 measures

PV = 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

[ryan_mayu]

@34583458hhdnsk 

your screenshot is too small. could you pls paste your sample data here and also provide the expected output?

[34583458hhdnsk]

@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

[ryan_mayu]

@34583458hhdnsk 

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

[34583458hhdnsk]

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

[34583458hhdnsk]

@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

[ryan_mayu]

what's the expected output based on this sample data?

[34583458hhdnsk]

[ryan_mayu · Accepted solution]

@34583458hhdnsk 

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 speed

pls see the attachment below