DAX Formula to get Sum- Group by

[Patv]

Hello Friends, I am new to DAX looking for help.   I have attached my questions in attached pictures and discribed my current situation. Please help me writing DAX and achive my desired result. I request you to write me a step by step guide if possible asas I am new to DAX  so it's better to get  more detail solution. Thank you for your help and time in advance!.

I have added pbix file at google drive just incase  --

https://drive.google.com/file/d/1-zlbHeEMALQfQ6iRYd7pfm_qBq7FstBU/view?usp=sharing

Question: 

Question: I want to pull data from Project, Milestone and Finance tables in my output result. Currently, I want sum of Allocation by funding type and year and Project ID. I want to create 2020_cash_allocation, 2020_Capital_Allocation, 2021_Cash_ALlocation, 2021_Capital_Allocaton, 2022_Cash_Allocation, 2022_Capital_Allocation, etc.. columns in output result.

I have created a Measure like e.g. but DID NOT WORK...
2020_Cash_allocation = Calculate(Sum(Finance[Allocation]),Finance[FundingType]="Cash",Finance[Year] = 2020)
2020_Cash_allocation = Calculate(Sum(Finance[Allocation]),ALLEXCEPT(Finance,PROJECT[ProjectID]),Finance[FundingType]="Cash",Finance[Year] = 2020)
2020_Cash_allocation = Calculate(Sum(Finance[Allocation]),ALLEXCEPT(Finance,Finance[ProjectId],Finance[Year],Finance[FundingType]),Finance[FundingType]="Cash",Finance[Year] = 2020)
I want to pull ProjectID, Name, Projectmanager from Project Table, MSDate from Milestone table and sum(allocation) group by projectid, year, fundingtype from Finance table. I am sure I am missing something in Dax formula and that is where I need help from experts.

Note: Projectid = 8 is not in Finance table but I still want to see it in final output.

[v-angzheng-msft · Accepted solution]

Hi, @Patv 

You are no doubt an expert in asking questions and I would be more than happy to help you look at this issue.
Please check out the solution below. You just need to create measures like this:

2020_Cash_Allocation = 
VAR _currentProjectID=MAX('PROJECT'[PROJECTID])
VAR _YEAR=2020
VAR _TYPE="Cash"
RETURN 
CALCULATE(
    SUM('FINANCE'[ALLOCATION]),
    FILTER(ALL('FINANCE'),'FINANCE'[PROJECTID]=_currentProjectID&&'FINANCE'[YEAR]=_YEAR&&'FINANCE'[FUNDINGTYPE]=_TYPE)
    )

The rest of the measures are exactly the same, you just need to modify the year and type.

If you don't want to create that many measures, but want the measures to dynamically determine the year and type, then you create a calculation table as follows to summarize the year and type in the financial table,

Year-Type = 
SUMMARIZE('FINANCE',[YEAR],[FUNDINGTYPE])

then create the following measures and create a slicer for the year and type, select the year and type you want to see, and voila, you only need one measure.

_SelectedValue = 
VAR _Year=SELECTEDVALUE('Year-Type'[YEAR])
VAR _Type=SELECTEDVALUE('Year-Type'[FUNDINGTYPE])
VAR _currentProjectID=MAX('PROJECT'[PROJECTID])

VAR _Result=
CALCULATE(
    SUM('FINANCE'[ALLOCATION]),
    FILTER(ALL('FINANCE'),'FINANCE'[PROJECTID]=_currentProjectID&&'FINANCE'[YEAR]=_Year&&'FINANCE'[FUNDINGTYPE]=_Type)
    )
return _Result

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[v-angzheng-msft · Accepted solution]

Hi, @Patv 

You are no doubt an expert in asking questions and I would be more than happy to help you look at this issue.
Please check out the solution below. You just need to create measures like this:

2020_Cash_Allocation = 
VAR _currentProjectID=MAX('PROJECT'[PROJECTID])
VAR _YEAR=2020
VAR _TYPE="Cash"
RETURN 
CALCULATE(
    SUM('FINANCE'[ALLOCATION]),
    FILTER(ALL('FINANCE'),'FINANCE'[PROJECTID]=_currentProjectID&&'FINANCE'[YEAR]=_YEAR&&'FINANCE'[FUNDINGTYPE]=_TYPE)
    )

The rest of the measures are exactly the same, you just need to modify the year and type.

If you don't want to create that many measures, but want the measures to dynamically determine the year and type, then you create a calculation table as follows to summarize the year and type in the financial table,

Year-Type = 
SUMMARIZE('FINANCE',[YEAR],[FUNDINGTYPE])

then create the following measures and create a slicer for the year and type, select the year and type you want to see, and voila, you only need one measure.

_SelectedValue = 
VAR _Year=SELECTEDVALUE('Year-Type'[YEAR])
VAR _Type=SELECTEDVALUE('Year-Type'[FUNDINGTYPE])
VAR _currentProjectID=MAX('PROJECT'[PROJECTID])

VAR _Result=
CALCULATE(
    SUM('FINANCE'[ALLOCATION]),
    FILTER(ALL('FINANCE'),'FINANCE'[PROJECTID]=_currentProjectID&&'FINANCE'[YEAR]=_Year&&'FINANCE'[FUNDINGTYPE]=_Type)
    )
return _Result

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Patv]

Hi v-angzheng-msft,  Thank you so much for your reply. I liked the way you described solution and I created a one samle Measure called "2020_cash_allocation" as you mentioned and pulled it in a table. I get the Allocation value correct BUT I don't get all 8 rows  that I have in my Project Table. I get only ProjectId 2 & 3 that has Year = 2020 and Allocation = "Cash". I wanted to see all 8 projects from Project table as per my attachement. Could you please let me know how to get all rows from Project table  regardless if there is a matching projects in Finance or Milestone table? Thank you.

I have added pbix file at google drive just incase  --

https://drive.google.com/file/d/1-zlbHeEMALQfQ6iRYd7pfm_qBq7FstBU/view?usp=sharing

[v-angzheng-msft · Accepted solution]

Hi, @Patv 

Select Show items with no data from the drop-down option in the first field(ProjectID) of the visual

Best Regards,
Community Support Team _ Zeon Zheng

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

[Patv]

Thank  you v-angzheng-msft  for replying. That works as I was looking for. Great help!

[amitchandak]

@Patv , My advice would be to create a separate year table (Join with year of table and try measure like

Calculate(Sum(Finance[Allocation]),filter(Finance, Finance[FundingType]="Cash") ,Year[Year] = 2020)

or

Calculate(Sum(Finance[Allocation]),filter(Finance, Finance[FundingType]="Cash") ,filter(all(Year) , Year[Year] = 2020))

Use year from Year table and fund type from finance on the column of the matrix with this measure

Calculate(Sum(Finance[Allocation]),filter(Finance, Finance[FundingType]="Cash") ,

filter(all(Year) , Year[Year] = max(Year[Year] ) ))

[Patv]

Thank you Amit for your reply.  I can use the DAX formula you provided above but  I am  able to get ONLY rows from finance table where allocation columns has values which are projectid 2, 3, 5, 6.  My requirement is to get all the rows from Project table and matching values from Milestone table and Finance table. If projectid doesn't exit in Milestone and Finance table then keep value null. ( just like making Left outerjoin between Project and Milestone table and left outer join between Project and finance table).

I have posted Pbix file to google drive if that helps to create DAX.

https://drive.google.com/file/d/1-zlbHeEMALQfQ6iRYd7pfm_qBq7FstBU/view?usp=sharing