Forum Discussion
Spend per card calculation
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?
Hi 34583458hhdnsk ,
You can do it by using this DAX measure spend per card = DIVIDE([PV], [Number of Cards]), but before adding the custom column, confirm that PV and Number of cards columns are visible and populated in the preview after the step pivot, if not, the pivot step needs to be corrected.
Thanks and regards,
Chaithra.
6 Replies
- BhavinVyas3003
Super User
- 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.
It does 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)
v-echaithraCommunity Support
Hi 34583458hhdnsk ,
You can do it by using this DAX measure spend per card = DIVIDE([PV], [Number of Cards]), but before adding the custom column, confirm that PV and Number of cards columns are visible and populated in the preview after the step pivot, if not, the pivot step needs to be corrected.
Thanks and regards,
Chaithra.- v-echaithra
Hi 34583458hhdnsk ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra. - v-echaithra
Hi 34583458hhdnsk ,
If the provided answer was helpful, please mark it as "Accept as solution" and click "Yes" if you found it helpful, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Chaithra. - v-echaithra
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra.
