Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a calculation based on a field in another column?

Hello, 

Would anyone know the DAx formula I may need to use for the below:

 

I'm trying to create a calculation (I think) based off a field in a different column

 

Currently my setup is:

GROUPTEAMCATEGORYTIMEMILESTONETOTAL
AUKMANAGEMENT1  
A   AUTHORISED200
A   PREPARED100
A   DELIVERED50
BUSMANAGEMENT5  
B   AUTHORISED100
B   PREPARED23
B   DELIVERED12
AUKADMIN4  
A   AUTHORISED50
A   PREPARED25
A   DELIVERED10

 

I'm trying to create a calculation where it would calculate the total amount of time per team/category combo but this would be divided by the "Authorised" milestone field total in particlar so for example (US / MANAGEMENT) 5 / (AUTHORISED) 100 = 0.05.

 

I'm thinking this is possibly an IF statement job but not sure. Essentially I am thinking it should be something which would mean 'if Milestone is "Authorised" then return the Total field value and divide it by Time field value' or 'Ignore Prepared and Delivered Milestones and only retun the total for Authorised to be divided by Time'?

 

The reason the table looks like the above is because I joined two seperate reports together and this was the easiest way to allow me to create a slicer for my page based on group. These appear on my page as two seperate tables as it is anyway, I just wanted to try and add an additional bit where you would get the above calculation for specific percentages.  

I would be grateful for any suggestions, 

Many thanks.


Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may go to 'Query Editor' and create an index column as below.

     

    You can try the following measures.

    Total measure = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    IF(
        'Table'[MILESTONE]="AUTHORISED",
        CALCULATE(
            SUM('Table'[TOTAL]),
            'Table'[Index]=EARLIER('Table'[Index])
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Percentage = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    var _index = 
    CALCULATE(
        MAX('Table'[Index]),
        FILTER(
            ALL('Table'),
            'Table'[Index]<EARLIER('Table'[Index])&&
            'Table'[GROUP]=EARLIER('Table'[GROUP])&&
            'Table'[TEAM]<>""&&
            'Table'[CATEGORY]<>""
        )
    )
    var _time = 
    LOOKUPVALUE('Table'[TIME],'Table'[Index],_index)
    return 
    IF(
        [MILESTONE]="AUTHORISED",
        DIVIDE(_time,[TOTAL])
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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

     

     

     

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may go to 'Query Editor' and create an index column as below.

     

    You can try the following measures.

    Total measure = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    IF(
        'Table'[MILESTONE]="AUTHORISED",
        CALCULATE(
            SUM('Table'[TOTAL]),
            'Table'[Index]=EARLIER('Table'[Index])
        )
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Percentage = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    var _index = 
    CALCULATE(
        MAX('Table'[Index]),
        FILTER(
            ALL('Table'),
            'Table'[Index]<EARLIER('Table'[Index])&&
            'Table'[GROUP]=EARLIER('Table'[GROUP])&&
            'Table'[TEAM]<>""&&
            'Table'[CATEGORY]<>""
        )
    )
    var _time = 
    LOOKUPVALUE('Table'[TIME],'Table'[Index],_index)
    return 
    IF(
        [MILESTONE]="AUTHORISED",
        DIVIDE(_time,[TOTAL])
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much for this, oddly, when I try and run the percentages measure - I get an error saying not enough memory to perform - could this be due to the size if the dataset?

      • richbenmintz's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        Hi Anonymous,

         

        Are you able to provide a sample pbix, really need to see what your data actually looks like.