Forum Discussion

UrAvgWally's avatar
UrAvgWally
Icon for Helper I rankHelper I
4 years ago

Help required for Sum , with breakdown by project

Hi All,

 

I have a table , UnpivotCP with a list of projects , for this example I filtered on one project :

 

The table has multiple projects, funders and has values for period 1 to 13.

 

I have used formula's like  , where Setup[CP], indicates the current period, for this example 5.

CPActuals = CALCULATE(SUM(UnpivotCP[Value]),FILTER(ALL(UnpivotCP),UnpivotCP[PeriodNo]= MAX(Setup[CP])))
 
I have a KeyTable, Say RSKCL which has a Funder Column and department name , The department name being the slicer and so I can filter on the below table :
The Actual is based on the formula above, the RF is based on another table with same type of data but with budgets and so the same formula is used.
 
 

So the breakdown of the Amounts work fine, The Actual column is based on the DAX formula of CPActuals for this instance.

 

But when I try to create a number table, with the breakdown by project rather than Funder, I get the total amount , rather than the amount for each project as per below :

 

Any advice on how to make my dax formula work, not only by Funder but by project so I can imitate the same by project?

 

I would still like to use a slicer of the RSKCL[Department], which has an RSKCL[Funder] relationship with Unpivot[Funder]

 

Any help would be appreciated. Also, let me know if any further clarifications are needed 🙂

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    UrAvgWally So normally when you see results like that it means that you are missing a relationship in your model.

    • UrAvgWally's avatar
      UrAvgWally
      Icon for Helper I rankHelper I
      4 years ago

      Hi Greg_Deckler , thanks for getting back to me I tried doing this : 

       

      Step 1- Make a table with Summarize formula as per below :

       

       

       

      Theen as Step 2, Created relationships:

       

      Created a relationship with RSKCL for the Funder, and created a relationship of the ProjectNo. With UnPivotCP and UnPivotRF.

       

      Step 3:

      I used the table i created with the summarize formula,Table ProjectNoKey for the project No as the Column criteria when creating a table visual, and added to that two measures Which is:

       

      CPActuals = CALCULATE(SUM(UnpivotCP[Value]),FILTER(UnpivotCP,UnpivotCP[PeriodNo]= MAX(Setup[CP])))
       
      CPRF = CALCULATE(SUM(UnpivotRF[Value]),FILTER(UnpivotRF,UnpivotRF[PeriodNo]= MAX(Setup[CP])))
       
      And the result is still same as per below : 
       

       

      Any idea what I am doing wrong ?