cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sforten
Frequent Visitor

Count Months Above Target with Hierarchy Roll Up

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.

 

MonthStoreDistrictMarketActualTarget

 

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?
1 ACCEPTED SOLUTION

Hi @sforten ,

 

 

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

View solution in original post

10 REPLIES 10
lc_finance
Solution Sage
Solution Sage

Hi @sforten ,

 

 

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

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?

Hi @sforten ,

 

 

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

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?

Hi @sforten ,

 

 

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

Here is a link to a sample file 

 

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

 

Thanks!

Hi @sforten ,

 

 

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

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!

sforten
Frequent Visitor

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

Hi @sforten ,

 

 

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

 

Do not hesitate if you have any more questions,

 

LC

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!