Count Months Above Target with Hierarchy Roll Up

[Anonymous]

Hello,

Trying to get a simple count of months above target from my dataset, but having an issue with it rolling up through my hierarchy.  Here is a basic example of my dataset columns which contains actuals and targets for a rolling 12 months.

Month

Store

District

Market

Actual

Target

I have a measure that calculates % to Target and I want to see how many months out of the year each store/district/market achieved their target.  Right now I am using the following - 

% to Target = Divide(sum('Table'[Actual]),sum('Table'[Target]))

MonthsAtTgt = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1))

This works well at the store level but does not produce the correct count for hierarchies above - for example, if I filter to a single District or Market the result is always the max of 12.

What am I missing?

[lc_finance]

Hi @Anonymous ,

Currently the formula checks for months with Actuals higher than Targets. As you say, this works well at the store level.

For example, Store 1 has January Actuals higher than Target. Then, Store 2 has February Actuals higher than Target. Then, Store 5 has March Actuals higher than Target. etc.

When filtering at the market level, all these stores are included in your selection, so you have always all months exceeding the target.

You can fix that by:

- adding up all the stores when checking at district level

- adding up all the stores and all the districts when checking at market level

% to Target District = calculate(Divide(sum('Table'[Actual]),sum('Table'[Target])),ALL('Table'[Store]) )

MonthsAtTgt District = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1),ALL('Table'[Store]) )

% to Target Market = calculate(Divide(sum('Table'[Actual]),sum('Table'[Target])),ALL('Table'[Store], 'Table'[District]) )

MonthsAtTgt Market = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1),ALL('Table'[Store]), 'Table'[District]) )

Does this help you?

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

It is somewhat of a workaround to get the number, but my goal would be to have one measure that calculates and filters with the hierarchy filters.  End results is a card that displays the count of months above target that changes with the hierarchy filters whether it's drilled all the way down to store or left at Market.  Is this possible?

[lc_finance]

Hi @Anonymous ,

No problem, that's also possible. We can use the function ISFILTERED to check out which column is filtered, and based on that show the correct calculation.

You can find the updated DAX formulas below.

% to Target = 

VAR forStore = Divide(sum('Table'[Actual]),sum('Table'[Target]))
VAR forDistrict = calculate(Divide(sum('Table'[Actual]),sum('Table'[Target])),ALL('Table'[Store]) )
VAR forMarket = calculate(Divide(sum('Table'[Actual]),sum('Table'[Target])),ALL('Table'[Store], 'Table'[District]) )

RETURN IF(ISFILTERED('Table'[Market]),
        forMarket ,
        IF(ISFILTERED('Table'[District]),
        forDistrict ,
        forStore ) )

MonthsAtTgt = 

VAR forStore = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1))
VAR forDistrict = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1),ALL('Table'[Store]) )
VAR forMarket = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1),ALL('Table'[Store]), 'Table'[District]) )

RETURN IF(ISFILTERED('Table'[Market]),
        forMarket ,
        IF(ISFILTERED('Table'[District]),
        forDistrict ,
        forStore ) )

Is this what you are looking for?

Do not hesitate if you have more questions,

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

Appreciate the help so far @lc_finance !  The first DAX formula worked like a charm, but I'm having an issue with the second one.  I was able to create the MonthsAtTgt measure just fine but am now receiving an error when attempting to create a visual with it.

Error: Couldn't load data for this visual

MdxScript(Model) (17, 133) Calculation error in measure 'Table'[% to Target]: Cannot convert value 'Foothills' of type Text to type True/False.

'Foothills' is one of my District names and the name of the District of the first store on the data pull so appears it is getting hung up right away.  Any thoughts?

[lc_finance]

Hi @Anonymous ,

what about modifying as follow:

MonthsAtTgt = 

VAR forStore = calculate(distinctcount('Table'[Month]),filter('Table','Table'[% to Target]>=1))
VAR forDistrict = calculate(distinctcount('Table'[Month]),'Table'[% to Target]>=1,ALL('Table'[Store]) )
VAR forMarket = calculate(distinctcount('Table'[Month]),'Table'[% to Target]>=1,ALL('Table'[Store]), 'Table'[District]) )

RETURN IF(ISFILTERED('Table'[Market]),
        forMarket ,
        IF(ISFILTERED('Table'[District]),
        forDistrict ,
        forStore ) )

If it still doesn't work, could you share an example of the Power BI file?

That would make it a lot easier to find the problem

Regards

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

Here is a link to a sample file 

https://1drv.ms/u/s!AqL1thqMq4QImxXpRRQ-_Ao7ON-6?e=8vKYAg 

Thanks!

[lc_finance · Accepted solution]

Hi @Anonymous ,

you can find my updated solution here.

Based on your data model I fixed the MonthsatAccTgt measure and I also fixed a problem in the % to Target measure.

You can find the new measures here:

% to Target = 
VAR forStore = DIVIDE(SUM('Acc Performance'[Acc Actual]),sum(Quota[Target]))
VAR forDistrict = calculate(DIVIDE(SUM('Acc Performance'[Acc Actual]),sum(Quota[Target])),all('Hierarchy'[Store]))
VAR forMarket = calculate(DIVIDE(SUM('Acc Performance'[Acc Actual]),sum(Quota[Target])),all('Hierarchy'[Store],'Hierarchy'[District]))

Return IF(ISFILTERED('Hierarchy'[Store]),forStore,if(ISfiltered('Hierarchy'[District]),forDistrict,forMarket))

MonthsatAccTgt = sumx(values('Month'[Month]),IF([% to Target]>=1,1,0))

The MonthsatAccTgt can be very easy, as all the complicated work is done by the % to Target measure.

Let me know if this helps you,

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

Works like a charm, thank you @lc_finance!

One follow-up question to this.  The count works great as I filter using my Matrix visual in my main report - clicking on a Market/District/Store in the Matrix filters the MonthsatAccTgt measure to show how many months that hierarchy level has been above target.  However, if I filter to a specific Store/District using the Filter Pane (without clicking on it in the Matrix) the MonthsatAccTgt measure does not filter to the correct number.  Is this how that measure is supposed to work with this DAX expression, or is there one additional piece I am missing?

Thanks!

[Anonymous]

Figured out how the Matrix was impacting this.  Thanks again, @lc_finance 

[lc_finance]

Hi @Anonymous ,

Sorry for the late reply, I am glad you found a solution for it.

Do not hesitate if you have any more questions,

LC