Measure Error in MATRIX - Power BI

[pankajj]

I have 2 tables:
ACCOUNT BUDGET
this has many AC ID and Description and the budget for each year and respective month.

Other table is SALES DATA

Which has Shipped Amount, Remaining Amount (calculated) multiple dates as Desired Ship Date, Last Ship Date, Promise Date, Promise Del Date, Shipping Date and different different dates are used to measure different results.

Now I have a task to convert excel to POWER BI Report:
The Excel file i received, looks like below:

Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20
Budget Budget figures for the year under each respective Month for the financial year.
Past Due only under current month
Past Due (On Hold) only under current month
Actual Shipped Revenue Only under year to date rest $0
Booked Revenue (On Hold) (On Hold Forecast) Only under May-20 to Oct-20
Booked Revenue (forecast) Only under May-20 to Oct-20
Total

I have used following Measures :

PAST DUE LATE = CALCULATE(SUMX( Filter( ALL('SALES DATA'), AND('SALES DATA'[ON HOLD LATE] = "Late", 'SALES DATA'[REVENUE S]="Amp")), ('SALES DATA'[REMAINING VALUE]) ))

PAST DUE ON HOLD LATE = CALCULATE(SUMX( Filter(ALL('SALES DATA'),AND('SALES DATA'[ON HOLD LATE] = "On Hold - Late", 'SALES DATA'[REVENUE S]="Amp")), ('SALES DATA'[REMAINING VALUE]) ))

ACTUAL SHIPPED REVENUE = CALCULATE(SUM ('SALES DATA'[TOTAL_AMT_SHIPPED] ), Filter(ALL('SALES DATA'), AND( 'SALES DATA'[LAST_SHIPPED_DATE]>='SALES DATA'[FY 2020 START], 'SALES DATA'[LAST_SHIPPED_DATE]<='SALES DATA'[FY 2020 END]) ))

Created connection between ACCOUNT BUDGET and SALES DATA table in the modeling and connected AC ID to AC ID

Now in the Power BI Matrix i have used
ROW: AC ID (from ACCOUNT BUDGET)

COLUMN: Month-Year from ACCOUNT BUDGET table

VALUES:
BUDGET (from ACCOUNT BUDGET)
PAST DUE LATE (from SALES DATA)
PAST DUE ON HOLD LATE (from SALES DATA)
ACTUAL SHIPPED REVENUE (from SALES DATA)

All the measures are working but
In my Power BI Matrix, I am getting only correct budget value, and the total value for all the above Measures under each Month-Year (instead of getting Past Due only under current month, Monthly Actual Shipped Revenue and so on...

Is there is simple quick fix to my problem?

Will really appreciate help.

Thanks & best regards,

PG

[Greg_Deckler]

I don't understand what is wrong. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Maybe a measures total problem? Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[pankajj]

Thanks a ton Greg for your quick help.

I tried your solutions however i am still unable to match Excel results in Power Bi Metrix visual.

@edhans 

@v-yuta-msft 

Thank you very much for your response to my query.

Apologies for not providing the data file. The actual data is too large and complicated, the way it is created by my colleague in Excel.

I will create a sample file and upload it for your kind review and possible solution support.

Thank you very much once again. You are an excellent support to the Community.

Best regards,

PG

[pankajj]

@Greg_Deckler 

Good Evening Greg,

Sorry for the long pause. I have tried to reproduce the excel file to give some context to my data and expected result in Power BI. Below is the link to my file for your kind review and humble support as always.

https://www.dropbox.com/scl/fi/y48wrna1jr61ghl8eljxf/RR.xlsx?dl=0&rlkey=8vgh4hha6my50q4ht8h3w1r35

Thanks and best regards,

PG 

[pankajj · Accepted solution]

Thank you for the guidance. I was able to fix my problem by creating additional measures and a Summarize Table.