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

Measure in a matrix to show Peak or Max Usage % from past 6 months

Hi All, I'm at my wit's end. In need of some urgent help to close out this project with this relatively simple scenario (at least at first).

 

In Short: Need to show in a matrix the Universal Peak Usage % from the past 6 months. ( Show what was the peak usage over the past 6 month from Today()) I have created a Tool Tip that shows a great sparkline over time with the same measure, but I am struggling to get the correct Peak/Max to show within my timeframe within the equation. 

Notes:

- The Measure I call the "Universal Peak Usage %"  is based on the Date field called "DateDim[Date]"

- The Measure in summary, calls a certain product's pre-made usage measure based on the filter on it's name - "new_product[new_name]". I've used a SWITCH Statement for this instead of IF. 

- The measure is in a Matrix that is connected to a Time Slicer - Date field is called "Orginal Renewal Date" - it shows you the list of companies and products that Renew within the selected month - this is not the same date field for my measure and should be kept separate with no impact. To my knowledge they are not connected in anyway. 

- Need to write the Filter/Slicer within the Measure - (i think)

- I do not have access to my data model - I'm limited to only work with creating new measures, so I can't create new columns.

 

Here is my Measure formula: I'm more interested in my RETURN equation. How do I tell it to select the Peak. I've attempted some Max scenarios, but again my Peak Usage Ratio's are all Measures. 

Universal Peak Usage % = 
VAR EN_Ratio =
    CALCULATE ( [Peak Usage Ratio %], new_product[new_name] = "enersight")
VAR VN_Ratio =
    CALCULATE (
        [Peak Usage Ratio %],
        new_product[new_name] = "Val Nav",
        CRM_LICENSE_DATA[PRODUCT] = "VALNAV",
        LICENSE[PRODUCT] = "VALNAV"
    )
VAR EM_Ratio =
    CALCULATE ([Peak Usage Ratio %],
        new_product[new_name] = "esi.manage",
        CRM_LICENSE_DATA[PRODUCT] = "ESIMANAGE",
        LICENSE[PRODUCT] = "ESIMANAGE"
    )
VAR RTX_Ratio =
    CALCULATE ( [RTXRatio], new_product[new_name] = "Execute - RTX")
VAR Budget_Ratio =
    CALCULATE ( [BudgetRatio], new_product[new_name] = "Execute - Budget")
VAR AFE_Ratio =
    CALCULATE ( [AFERatio], new_product[new_name] = "Execute - AFE")
VAR Select_Product =
    SELECTEDVALUE ( new_product[new_name] )

RETURN
 IF (
            HASONEVALUE( Account[Account Name] ) ||
            HASONEVALUE(Account[Parent Customer] )||
            HASONEVALUE(new_product[new_name] )
        ,
            CALCULATE (
            SWITCH (
                Select_Product,
                "Execute - RTX", RTX_Ratio,
                "Execute - Budget", Budget_Ratio,
                "Execute - AFE", AFE_Ratio,
                "enersight", EN_Ratio,
                "Val Nav", VN_Ratio,
                "esi.manage", EM_Ratio,
                BLANK ()
            ), ALLEXCEPT(DateDim, DateDim[Date]), FILTER(DateDim, DateDim[Date] >= EDATE(TODAY(), -6))
            )
        )

 

 

Below are the screenshots they show two examples where the % displayed in the Column called "Peak Usage % in Last 6 Mnths" (this is the Universal Peak Usage % Dax equation I posted above, it is also used in the tooltip and in the table to the right) is not the peak within those 6 months. The Tooltip is filtered DateDim[Date] - Advanced Filter: In the last 6 Months. 
Example 1: peak usage % should show 100%
Example 2: Peak usage % should show Also 100%, from September. 

I've included the smaller table to show you that it is calling the correct numbers. 

Example 2 wrong %.PNG

 

Example 1 wrong %.PNG

 

 

Thanks in advance for your help. I know dangerously just enough to get myself stuck and I have no idea how easy or how difficult it is to figure this one out. 

 

I appreciate all the help (as fast as possible)

 

 

Best!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Measure in a matrix to show Peak or Max Usage % from past 6 months

Hi @rebecca_allison ,

 

You could refer to the following DAX and add your own time filter in it.

Peak Usage % in last 6 months=
DIVIDE (
    COUNTX ( FILTER ( 'Table', [Universal Peak Usage %] = 1 ), [Universal Peak Usage %] ),
    COUNTX ( 'Table', [Universal Peak Usage %] )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Measure in a matrix to show Peak or Max Usage % from past 6 months

Hi @rebecca_allison ,

 

You could refer to the following DAX and add your own time filter in it.

Peak Usage % in last 6 months=
DIVIDE (
    COUNTX ( FILTER ( 'Table', [Universal Peak Usage %] = 1 ), [Universal Peak Usage %] ),
    COUNTX ( 'Table', [Universal Peak Usage %] )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 395 members 4,254 guests
Please welcome our newest community members: