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 DataIDStart DateEnd DateValue 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:IDStart DateEnd DateValueJanuary 2020February 2020March 2020April 202011/1/202031/3/2020900300300300 21/2/202030/4/20201200 40040040031/1/202031/1/202010001000 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.

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.

Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Matrix table total incorrect

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.

Need Help

v-zhenbw-msft
6 years ago
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.
Matrix table total incorrect.pbix86 KB

8 Replies

amitchandak
Super User
6 years ago
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
  Not applicable
  6 years ago
  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.
v-zhenbw-msft
Community Support
6 years ago
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
  Not applicable
  6 years ago
  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
    Community Support
    6 years ago
    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.
    Matrix table total incorrect.pbix86 KB

Forum Discussion

Matrix table total incorrect

8 Replies

Related content

Matrix table

Matrix Totals Incorrect

Matrix table

KPI level matrix table

Matrix table in Power bi

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