topic Re: Help with PVM totals (not adding column correctly) plus a twist in DAX Commands and Tips

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

Neogeo2 — Wed, 18 Oct 2023 23:14:48 GMT

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 )

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

DallasBaba — Thu, 19 Oct 2023 05:54:49 GMT

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

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

Neogeo2 — Thu, 19 Oct 2023 06:06:47 GMT

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.

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

DallasBaba — Thu, 19 Oct 2023 15:57:07 GMT

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