Filter and Calculate percentages

[PabloGiraldo]

Hi All,

I have the table below with mahours that get updated weekly and monthly. Based on the "Total MH Forecasted" which get updated monthly, I want to be able to somehow filter/click on a card that will give me % Complete to Date each week when data gets updated.

So if I were to send an update on 11/7/2020, I would like for the filter/card to use the last "Total MH Forescated" value (in this case 995,156) and 1) Divide by the sum of each Category ("Management," "Field," etc.) to get % Complete to Date per Category and 2) Divide by the Total sum of all Categories to get Total % Complete to Date.

Open to any ideas on how this could be presented

.

Thanks,

Pablo

[v-alq-msft · Accepted solution]

Hi, @PabloGiraldo 

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

Table:

Date(a calculated table):

Date = DISTINCT('Table'[Week Ending])

You may create three measures as below.

Management measure = 
IF(
    HASONEVALUE('Date'[Week Ending]),
    IF(
        ISFILTERED('Table'[Week Ending]),
        SUM('Table'[Management]),
        DIVIDE(
            CALCULATE(
                SUM('Table'[Management]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])
                )
            ),
            CALCULATE(
                SUM('Table'[Total MH Forecasted]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]=
                    CALCULATE(
                        MAX('Table'[Week Ending]),
                        FILTER(
                            ALL('Table'),
                            [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])&&
                            [Total MH Forecasted]>0
                        )
                    )
                )
            )
        )
    )
)

Field measure = 
IF(
    HASONEVALUE('Date'[Week Ending]),
    IF(
        ISFILTERED('Table'[Week Ending]),
        SUM('Table'[Field]),
        DIVIDE(
            CALCULATE(
                SUM('Table'[Field]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])
                )
            ),
            CALCULATE(
                SUM('Table'[Total MH Forecasted]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]=
                    CALCULATE(
                        MAX('Table'[Week Ending]),
                        FILTER(
                            ALL('Table'),
                            [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])&&
                            [Total MH Forecasted]>0
                        )
                    )
                )
            )
        )
    )
)

SubA measure = 
IF(
    HASONEVALUE('Date'[Week Ending]),
    IF(
        ISFILTERED('Table'[Week Ending]),
        SUM('Table'[SubA]),
        DIVIDE(
            CALCULATE(
                SUM('Table'[SubA]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])
                )
            ),
            CALCULATE(
                SUM('Table'[Total MH Forecasted]),
                FILTER(
                    ALL('Table'),
                    [Week Ending]=
                    CALCULATE(
                        MAX('Table'[Week Ending]),
                        FILTER(
                            ALL('Table'),
                            [Week Ending]<=SELECTEDVALUE('Date'[Week Ending])&&
                            [Total MH Forecasted]>0
                        )
                    )
                )
            )
        )
    )
)

Finally you may use the 'Week Ending' from 'Date' to filter the 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.

[PabloGiraldo]

@v-alq-msft 

Is it possible to modify the measures to be able to get month to date, and yeart to date? Right now the measures give us Total Project to Date. 

Thanks!

[PabloGiraldo]

Thank you. Not sure how you got here but it was very helpful.

[ToddChitt]

@PabloGiraldo Can you explain a little more? 

Am I understanding this correctly:

Given a date slice of 10/17/2020, you want to divide the SUM of Management Hours UP THROUGH that date by the LAST NON-ZERO value of the second column. 

So: 3 * 600 + 5 * 640 = 5000

5000 / 995156 = .005024

Is that correct?

[PabloGiraldo]

No. I still want to include all Management Hours up to 11/7/2020 and divide by the LAS NON-ZERO value of the second column.

So: 3*600 + 5*640 + 680 +720 +800 = 7200

7200 / 995156 = .00723

Thanks!

[ToddChitt]

Ah, you are talking about a date slice of 11/7 and I was talking about 10/17.

Your math makes sense.

I would attack this in two parts, then bring them together:

Latest Forecast = CALCULATE ( LASTNONBLANK ( [Total MH Forecast] ), [Total MH Forecast] <> 0 )

LASTNONBLANK function (DAX) - DAX | Microsoft Docs

Then:

YTD Management Hours = TOTALYTD ( SUM(Managemnt), Dates[Date] )

(This assumes you have a Date dimension attached.

Then finally:

Percent Complete = DIVIDE ( [Latest Forecast], [YTD Management Hours], 0 )

Test it out by adding these measure to a table that is sorted by Date/Week to check the logic of each.

Hope this helps.

[PabloGiraldo]

No luck here. Did not work.

I would like to have all my data in one table then use a filter that will give me % Complete to Date value.

Thanks.

[PabloGiraldo]

I will give it a shot in the next 30 min and reply to see if it worked.

Thanks!