Create a calculation based on a field in another column?

[Anonymous]

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:

GROUP	TEAM	CATEGORY	TIME	MILESTONE	TOTAL
A	UK	MANAGEMENT	1
A				AUTHORISED	200
A				PREPARED	100
A				DELIVERED	50
B	US	MANAGEMENT	5
B				AUTHORISED	100
B				PREPARED	23
B				DELIVERED	12
A	UK	ADMIN	4
A				AUTHORISED	50
A				PREPARED	25
A				DELIVERED	10

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.

[v-alq-msft · Accepted solution]

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]

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]

Hi @Anonymous,

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