Forum Discussion

SebL's avatar
SebL
Frequent Visitor
5 years ago

YTD Value in Matrix Total

Hi Folks, 

Looking for a solution for the following DAX measure: 

Frequency =
IFERROR(
Count(Account[ReferenceNu])/Sum(CANSIM[BOTH])*12*100,
BLANK()
)


The IFERROR is a fix for Infinity results, as there is a delay in publication of data in the denominator (CANSIM) table. The *12 multiplier is applied to each month to make them comparable to yearly values. The issue I'm having is with the total in the Year / Month matrix for the current year. The YTD value should have a multiplier of 12 divided by the count of months in the CANSIM table so far this year.

I can't seem to figure out how to apply this logic. 

I'm also looking to do the same for my two measures that display a territory breakdown. These are both missing the IFERROR element as well. 

Frequency (NT) =
COUNTX(
Filter(
Account,
RELATED(ClaimantAccount[TerritoryID])=1
),
Account[AccountID]
)/Sum(CANSIM[NT])*12*100
 
Frequency (NU) =
COUNTX(
Filter(
Account,
RELATED(ClaimantAccount[TerritoryID])=2
),
Account[AccountID]
)/Sum(CANSIM[NU])*12*100

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi SebL 

    Not clear. Can you shre some sample data and the expected result based on that data?

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

  • SebL's avatar
    SebL
    Frequent Visitor
    5 years ago

    Hi AlB, thanks for the response. 

    Here are some screenshots for more context. 

     

    'Merged' column connects month/year between dbo_Event and CANSIMMock-up of the different ways I want to present the data.Tables schemarelationships