Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 @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

View solution in original post

10 REPLIES 10
lc_finance
Solution Sage
Solution Sage

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
Not applicable

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 @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
Not applicable

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 @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
Not applicable

Here is a link to a sample file 

 

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

 

Thanks!

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
Not applicable

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
Not applicable

Figured out how the Matrix was impacting this.  Thanks again, @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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.