Measure in Total Row

[DanielKunc]

Hi,

I'm creating report with Table where are several measures but Total Row shows incorrect numbers ( Measure result )

I'm using this measure:

Still to be invoiced ACY = IF( [Invoiced Line Amount ACY] = 0; [Line amount excl. VAT ACY] - [GL_Amount];[Invoiced Line Amount ACY Remaining])

 On row level it works perfectly, but it fails in Total row. In Column Still to be Invoiced ACY Celkem (Total) should be 169 784 (475 438 - 305 653), but it shows 172 104 (based on following measure). 

Could you please kindly advise how to solve this?

Thank You

BR, Daniel

[v-zhenbw-msft]

Hi @DanielKunc ,

Maybe the condition of IF function is wrong, you can try measure like this,

New Measure = 
IF([Invoiced Line Amount ACY Remaining]=0,[Invoiced Line Amount ACY Remaining],[Line amount excl. VAT ACY]-[GL_Amount])

The result like this,

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DanielKunc]

Hi @v-zhenbw-msft ,

sorry for late reply and thank you for your reply.

I've prepared PBIX file where are simplified columns (I'm using Measures with the same Measure names). The issue is with Grand Total of the Still to be invoiced ACY measure:

Still to be invoiced ACY = 
VAR LineAmountGL = ([Line amount ACY] - [GL_Amount])
VAR DivideLineAmountGL = ABS(DIVIDE([Line amount ACY],[GL_Amount])-1)
RETURN
IF(DivideLineAmountGL>0.02,[Invoiced Line Amount ACY Remaining],
IF([Invoiced Line Amount ACY] = 0, LineAmountGL,[Invoiced Line Amount ACY Remaining]))

The Grand total shows values based on this "formula"

And I would need to get sum of each row of this "formula" as it's in Excel Pivot table. 

Thank You very much

DAniel

[amitchandak]

@DanielKunc , Row context issue. Try like

Sumx(summarize(Table, table[parent_project_id], "_1",[Still to be invoiced ACY]),[_1])

[DanielKunc]

@amitchandak Thank You for your promt reply but it unfortunately doesn't work.

Parent Project ID is from [Projects], "sublevel" is from [Contracts detail]. Each measure used in this PivotTable is in [Contracts detail] and they are similar to each other, for example:

Line Amount excl. VAT ACY = SUMX('Contracts Detail';'Contracts Detail'[Line Amount Excl. VAT (REP) after Discount])

They work fine on row and total level. But Still to be invoiced ACY does not work on Total level.

Thank You