Matrix table total incorrect

[Anonymous]

Hi all,

I have been going through the different forums about matrix table total incorrect and having tried several methods, it still did not solve my problem. I thought I will post this to try to get some help from the community.

I have a data set with the following details:

Table name: Main Data

ID	Start Date	End Date	Value

I also have another Date table which simply lists down the dates from 1 July 2018 to 1 July 2025.

The intention is to estimate what will be the value that can be generated in each month based on the Start / End date. Consider the following:

ID	Start Date	End Date	Value	January 2020	February 2020	March 2020	April 2020
1	1/1/2020	31/3/2020	900	300	300	300
2	1/2/2020	30/4/2020	1200		400	400	400
3	1/1/2020	31/1/2020	1000	1000

So I have used the formula:

expected_value = 

sumx('Main Data',CALCULATE(sumx('Main Data','Main Data'[Value] /

if([Start Date-End Date Date Diff]=0,1,[Start Date-End Date Date Diff]+1)*

if(min('Main Data'[Start Date])>max(Dates[Date]),0,

if(max('Main Data'[End Date])<MIN(Dates[Date]),0,

if('Main Data'[End Date]<'Main Data'[Start Date],0,

if(min('Main Data'[Start Date])<MIN(Dates[Date]) &&
max('Main Data'[End Date])<MAX(Dates[Date]) &&
max('Main Data'[End Date])>MIN(Dates[Date]),
(DATEDIFF(MIN(Dates[Date]),max('Main Data'[End Date]),DAY)+1),

IF(min('Main Data'[Start Date])>MIN(Dates[Date]) &&
min('Main Data'[Start Date])<MAX(Dates[Date]) &&
max('Main Data'[End Date])>MAX(Dates[Date]),
(DATEDIFF(min('Main Data'[Start Date]),MAX(Dates[Date]),DAY)+1),

if(min('Main Data'[Start Date])>MIN(Dates[Date]) &&
max('Main Data'[End Date])<MAX(Dates[Date]),
(DATEDIFF(min('Main Data'[Start Date]),max('Main Data'[End Date]),DAY)+1),

IF(min('Main Data'[Start Date])<=MIN(Dates[Date]) &&
max('Main Data'[End Date])>=MAX(Dates[Date]),
(DATEDIFF(MIN(Dates[Date]),MAX(Dates[Date]),DAY)+1),

0)
))))))))))

This appears to give me the correct expected_value. However, when I put in a matrix table (Similar to the above), the TOTAL is not the same. I have tried to add another VAR = summarize ('Dates', 'Dates'[Date], "correct total', expected_value) and use the HASONEVALUE if statement, the TOTAL is still incorrect. Can someone help, please?

Thank you.

[v-zhenbw-msft]

Hi @Anonymous ,

We have a little confused about your issue. What are the fields in the Matrix table?

There are two ways to solve the Total incorrect.

The first way is to create a calculate column based on your IF logic, and then create a measure using SUM or SUMX function.

The second way is to add SUMX function outside your measure.

We create a sample you can refer:

average total = 
IF (
    ISINSCOPE ( 'Table'[Abnormal] ),
    SUMX (
        VALUES ( 'Table'[Year] ),
        CALCULATE ( [Measure] )
    ),
    SUMX (
        VALUES ( 'Table'[Abnormal] ),
        CALCULATE ( [Measure] )
    )
)

BTW, pbix as attached.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

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.

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.

[Anonymous]

Hi @v-zhenbw-msft, thank you for your reply. Please allow me to elaborate my challenge:

1. I have a table with the start date, end date, and value column. For example:

ID	Start Date	End Date	Value
1	1/7/2020	31/7/2020	100
2	1/7/2020	31/8/2020	200

2. What I need to calculate is, by assuming the value is distributed linearly across the start / end date, what is the expected value in each month.

the expected_value = [Value / (Start Date - End Date)] * Dates

3. I want to display the data as below:

Dates	July 2020	August 2020	September 2020	October 2020	TOTAL
expected_value	200 From ID 1: 100 From ID 2: 100	100 From ID 2: 100	0	0	300

The formula that I have used is:

sumx('Main Data',CALCULATE(sumx('Main Data','Main Data'[Value]/if([Start Date-End Date Date Diff]=0,1,[Start Date-End Date Date Diff]+1)*
if('Main Data'[Probability (%)]=100,0,

if(min('Main Data'[Start Date])>max(Dates[Date]),0,

if(max('Main Data'[End Date])<MIN(Dates[Date]),0,

if('Main Data'[End Date]<'Main Data'[Start Date],0,
if(min('Main Data'[Start Date])<MIN(Dates[Date]) &&
max('Main Data'[End Date])<MAX(Dates[Date]) &&
max('Main Data'[End Date])>MIN(Dates[Date]),
(DATEDIFF(MIN(Dates[Date]),max('Main Data'[End Date]),DAY)+1),

IF(min('Main Data'[Start Date])>MIN(Dates[Date]) &&
min('Main Data'[Start Date])<MAX(Dates[Date]) &&
max('Main Data'[End Date])>MAX(Dates[Date]),
(DATEDIFF(min('Main Data'[Start Date]),MAX(Dates[Date]),DAY)+1),

if(min('Main Data'[Start Date])>MIN(Dates[Date]) &&
max('Main Data'[End Date])<MAX(Dates[Date]),
(DATEDIFF(min('Main Data'[Start Date]),max('Main Data'[End Date]),DAY)+1),

IF(min('Main Data'[Start Date])<=MIN(Dates[Date]) &&
max('Main Data'[End Date])>=MAX(Dates[Date]),
(DATEDIFF(MIN(Dates[Date]),MAX(Dates[Date]),DAY)+1),

0)
))))))))))

However, when I try to display the TOTAL, it is not showing the correct value.

I am not able to transfer via One Drive so have created a sample file here: PBI File.

Can you please kindly help?

Thank you.

[v-zhenbw-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

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.

[Anonymous]

@v-zhenbw-msft , thank you for your help! I have now managed to implement your solution and it works for me. Thank you so much for your help!

[Anonymous]

Hi @v-zhenbw-msft, thank you for your suggestion and I have been taking parts of your suggestions to fix my problem. It is not an ideal solution as I have to display the table in two sections

Item	Jan	Feb	March

Total

I used the same formula for my calculation. For the TOTAL column, I added an additional SUMX outside. 

I am looking to try your method of cross-join but have not done so as I had a tight deadline. The amount of calculation needed also meant that my laptop was slowing down massively. But for now it has worked for me with the additional SUMX.

Thank you.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We have a little confused about your issue, why the following screenshot need to *Dates? What is the Dates?

Do you want to get the average of value between Start and End? Is it based on day or month?

We find a solution that you can refer.

1. Create a date table, then use the CROSSJOIN function to create a result table. It will get all dates from Start to End.

Result Table = 
SUMMARIZE(
    FILTER(
        CROSSJOIN('Dates table','Main Data'),
        'Main Data'[Start Date]<='Dates table'[Date] 
        && 'Main Data'[End Date]>='Dates table'[Date]
    ),
    [Date],
    [ID],
    [Value]
)

2. Create a month name column and a value column.

Month_name = YEAR('Result Table'[Date])&"-"&FORMAT('Result Table'[Date],"mmmm")

Column = 
var _distinct = CALCULATE(DISTINCTCOUNT('Result Table'[Date]),FILTER('Result Table',EARLIER('Result Table'[ID])='Result Table'[ID]))
return
'Result Table'[Value] / _distinct

3. At last we can create a matrix table based on result table.

If it doesn’t meet your requirement, could you please add the Measure you use to the PBIX file you share?

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.

BTW, pbix as attached.

[amitchandak]

@Anonymous , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

or this link

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

or

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix?dl=0

[Anonymous]

Hi @amitchandak, thank you for your suggestions. It does not appear to be something for my problem unfortunately. Thank you for taking the time to reply to my post.