cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VilleNummisalo Frequent Visitor
Frequent Visitor

Measure total is different to row total

Hello,
 
I have a rather common problem but I cannot implement any solutions correctly to my fuction.
 
The function works correctly, showing the right rows and totals in a table I use to visualize. However, the total of the table or an info card is wildly different.
 
Part of the fuction that I assume is causing the problem, parameters in bold:
 
[Total Order Value for late rows] * 'Penalty per day/week'[Penalty % per day/week Value] *

SUMX('PO data'; IF('PO data'[Delay in weeks] < 'Max penalty weeks'[Max penalty weeks Value] *-1;
 
'Max penalty weeks'[Max penalty weeks Value] ;  
 
SUM('PO data'[Delay in weeks])*-1));
1 ACCEPTED SOLUTION

Accepted Solutions
VilleNummisalo Frequent Visitor
Frequent Visitor

Re: Measure total is different to row total

Figured it out. Just had to do a second measure with SUMX and multiply the problematic measure it by 1.

5 REPLIES 5
kkuntner Frequent Visitor
Frequent Visitor

Re: Measure total is different to row total

Hi @VilleNummisalo ,

 

 If I understand correctly, you are trying to summarize something, while considerind an upper limit (max penalty weeks value). The odd think in your formula is that in the IF function the two options are the 'Max penalty weeks'[Max penalty weeks Value] and SUM('PO data'[Delay in weeks])*-1) : one is a single value while the other is a sum. In the summary row of your visual table, the SUMX will iterate through the whole 'PO data' table, and for each row it will sum up 'PO data'[Delay in weeks] again, so it will do a sum-of-sums. That might cause the values being widly off.

 

Removing the SUM might help, otherwise please try to decribe the business rule you want to achieve, it might help understanding your formula better.

VilleNummisalo Frequent Visitor
Frequent Visitor

Re: Measure total is different to row total

Thanks for the reply @kkuntner 

 

I did try to eliminate the second SUM, but the answer doesnt change. I posted the full function below and here's the explanation:

 

I want to calculate the potential delay penalty for each purchase order (PO) row. This depends on 3 parameters, is the delay based on weeks or days, what is the percentage of total order value, and what is the maximum time of delay in the vendor specific agreement.

 

There are different functions based on the first parameter, and the 3rd looks if you can use the column value or should you limit it to the given max value.

 

Also, I have some filters in the end, removing them doesnt help.

 

 

Delay penalty measure = CALCULATE(IF('Delay penalty per week or day'[Delay penalty per week or day Value] = 0;

[Total Order Value for late rows] * 'Penalty per day/week'[Penalty % per day/week Value] *

SUMX('PO data'; IF('PO data'[Delay in weeks] < 'Max penalty weeks'[Max penalty weeks Value]*-1; 'Max penalty weeks'[Max penalty weeks Value];
('PO data'[Delay in weeks])*-1));

[Total Order Value for late rows] * 'Penalty per day/week'[Penalty % per day/week Value] *

SUMX('PO data'; IF('PO data'[Delay] < 'Max penalty weeks'[Max penalty weeks Value]*-1; 'Max penalty weeks'[Max penalty weeks Value];
('PO data'[Delay])*-1)));

VALUES('PO data'[Time]); VALUES('Plant data'[Plant]); 'PO data'[Delay] < -2; VALUES('PO data'[Vendor Description]))
kkuntner Frequent Visitor
Frequent Visitor

Re: Measure total is different to row total

Hi @VilleNummisalo  ,

 

Do you know if the [Total Order Value for late rows] measure makes sense for your total row? It might be a measure that makes sense for each PO row, but in the summary row you don't have a single PO row.

 

In such cases I try to debug by simplifying the formula back to its simplest form, and adding additional rules/expressions incrementally. Try if the value makes sense if you keep only the [Total Order Value for late rows] in the IF clause, does it make sense for you total row?

 

VilleNummisalo Frequent Visitor
Frequent Visitor

Re: Measure total is different to row total

Hi, @kkuntner 

 

The total order value for late rows makes sense in this context, it might sound strange but in reality it's basically just value per row.  Just the base column can't be used in a measure, SUM produces the same output. The answer is also correct per row, but not in total. That's how I know the formula works in row context, and I did do it part by part to achieve this.

pic1.JPG

VilleNummisalo Frequent Visitor
Frequent Visitor

Re: Measure total is different to row total

Figured it out. Just had to do a second measure with SUMX and multiply the problematic measure it by 1.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,223 guests
Please welcome our newest community members: