Forum Discussion

pankajj's avatar
pankajj
Helper III
6 years ago
Solved

Measure Error in MATRIX - Power BI

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

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

4 Replies