Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Neogeo2
Regular Visitor

Help with PVM totals (not adding column correctly) plus a twist

Rates Column doesnt total correctly, the rows are applying the formula ok.Rates Column doesnt total correctly, the rows are applying the formula ok.

I currently have the Rates column in the brackets, its the Price variance of the PVM formula set. The rows is calculated correctly but not the column itself, which i believe is a common power BI feature.

 

How do i get it to add up the column instead of applying the formula to the row (Qty & Rate Variance)?

Here is the twist. What if i want to have the categories (currently plant and materials) dynamic eg what if i want to insert Customers in later or add address in or take Materials away...etc.

 

I'm such a noob, any help or tips would be awesome help!

 

Currently formula is:

PVM Rate $ Impact Var LM ROW =
if(
and(
sumx('Fact Freight Details','Fact Freight Details'[QTY])<>0,
'Fact Freight Details'[QTY LM]<>0
),
sumx(
'Fact Freight Details',
'Fact Freight Details'[QTY]
)*
'Fact Freight Details'[Var $/tn Actual LM],
0
)

 

 

3 REPLIES 3
DallasBaba
Skilled Sharer
Skilled Sharer

@Neogeo2 If you're experiencing issues with the column totals, it could be due to various factors. Kindly check to ensure your column totals are correct.

1 - Ensure that the data type of the 'Var $/tn Actual LM' column is set as a numeric type (e.g., decimal or currency)  

2 -Verify that there are no filters applied to the visual or report page that might be affecting the column totals.

3 - Check that the column total is not being affected by slicers, filters, or drill-through actions in your report.

4 -  Double-check the aggregation type of the 'Fact Freight Details'[Qty] and 'Fact Freight Details'[Var $/tn Actual LM] columns. They should be set to "Sum" or the appropriate aggregation method.

5 - Ensure there are no blank or null values in the 'Fact Freight Details'[Qty] and 'Fact Freight Details'[Var $/tn Actual LM] columns, as these can affect the calculations.

 

If you've confirmed that these factors are not causing the issue, and the row-level calculations are correct while the column totals are not, please provide me more details about the specific issue you're facing or any error messages you might be encountering.

 

Does this answer your question? Please mark my post as a solution!
Or did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Thanks

Thanks
Dallas
DallasBaba
Skilled Sharer
Skilled Sharer

@Neogeo2 First, Let's address the issue with calculating the total price variance. You can use SUMX to calculate the total Price Variance (PVM Rate $ Impact Var LM) for your data:

Total PVM Rate $ Impact Var LM = 
SUMX('Fact Freight Details', 'Fact Freight Details'[Qty] * 'Fact Freight Details'[Rate Variance])

Now, for the dynamic categories (Plant and Material), you can create a more flexible model

1 - Now, for your twist regarding dynamic categories (Plant and Material), you can create a more flexible model

2 - Create a "Data" table that contains the actual data, including a column that specifies the category for each row (e.g., 'Category' column).
3 -Establish a relationship between the "Category" table and the "Data" table using the 'Category' column.

4 - Modify your DAX measures to consider the selected category dynamically. For example:

Total PVM Rate $ Impact Var LM = 
VAR SelectedCategory = SELECTEDVALUE('Category'[Category])
RETURN
SUMX(
    FILTER('Data', 'Data'[Category] = SelectedCategory),
    'Data'[Qty] * 'Data'[Rate Variance]
)

 

  Let me know if this work
@ me in replies, or I'll lose your thread!!!  


Thanks

Thanks
Dallas

@DallasBaba Thansk for the suggestion on using sumx.

Unfortunatley its already using sumx. The rows are coming up correctly as in the screenshot. however the totals are not.

 

Let me digest a bit on the 2nd solution you mentioned, i'm still very new to this whole thing.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.