New Enterprise DNA Initiatives

Measure that caps total based on a grouping/ID

Hi Folks,

I’m having one of those days where my head is struggling to conceptualize DAX.

I need to create a measure where I have the Number of Occurrences * by the Cost Per Occurrence. So far, so straightforward. However, Occurrences are capped at 1000 per incident so the maximum cost will be 1000 * £50.00. Usually I’ll only have one row per Incident Number but occasionally they can be split over two or more rows so for INC588 it has two entries, one of 990 and one of 15.

Any help, as always is much appreciated.

Incident No Occurences Cost per Occurrence Cost
Inc588 990 £50.00 £49,500.00
Inc588 15 £50.00 £750.00
Inc589 500 £50.00 £25,000.00
Inc590 10 £50.00 £500.00

@DavieJoe Try something like this

MeasureR =
VAR GroupSum =
    CALCULATE (
        SUM ( Sheet1[Occurences] ),
        ALLEXCEPT ( Sheet1, Sheet1[Incident No] )
    )
VAR Conditionz =
    IF ( GroupSum > 1000, 1000, BLANK () )
VAR Result =
    Conditionz * SELECTEDVALUE ( Sheet1[Cost per Occurrence] )
RETURN
    Result 

1

Thanks.

2 Likes

@DavieJoe Here is the Power Query solution:

Attaching the file for your reference.PQ Solution.xlsx (16.7 KB) Book1.xlsx (9.3 KB)

Hope it helps

2 Likes

@MudassirAli you’re a top man! Much appreciated, I will check these out in the morning and close as a solution then (I’m sure it’s a mere formality)

Love this community!

1 Like

@MudassirAli

Ha! You beat me to it - I was just finishing my power query solution when I saw you posted yours. For the sake of completeness, and not letting it go to waste here’s my PQ solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLNrWwUNJRsrQ0AJKmBnoGINrEUsfUAMyO1UFSZGiKpMbcFFWBJVgO2RQjUx0DVFPAlhgiq4FZEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Incident No" = _t, Occurences = _t, #"Cost per Occurrence" = _t, Cost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Incident No", type text}, {"Occurences", Int64.Type}, {"Cost per Occurrence", Currency.Type}, {"Cost", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Incident No", "Cost per Occurrence"}, {{"Total Occurrences", each List.Sum([Occurences]), type nullable number}, {"Total Costs", each List.Sum([Cost]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Max Occurrences", each 1000),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Adj. Total Cost", each if [Total Occurrences] >= [Max Occurrences] then [Max Occurrences] * [Cost per Occurrence] else [Total Costs])
in
    #"Added Custom1" 

image

  • Brian

eDNA forum – Capped Occurrences Solution.pbix (21.2 KB)

3 Likes

@BrianJ I see what you did there. Never a dull moment with PQ & DAX :smile:

2 Likes

Fabulous chaps!

I’ll take a look at the Power Query solutions, I’ve had to build something in DataFlows and my queries are completed so unsure if I’ll go back and change anything there.

Will definitely look through all the suggested ways of solving this for the learnz!

Really appreciated @BrianJ & @MudassirAli

2 Likes

@MudassirAli Your solution work 95% of the way for me as you can see. My hourly cost is actually £57.60 as went for simplicity with £50 in the example I posted.

In the picture I’ve posted I get the cap applied to the Total column at the bottom, is there a way that the measure can sum up the incidents also? I’d like to use the Measure in a KPI card also but it will obviously apply the cap there also.

Hope I made sense. Thanks again.

MeasureR =
VAR GroupSum =
CALCULATE (
SUM ( Sheet1[Occurences] ),
ALLEXCEPT ( Sheet1, Sheet1[Incident No] )
)
VAR Conditionz =
IF ( GroupSum > 1000, 1000, TotalOccurencesMeasure )
VAR Result =
Conditionz * SELECTEDVALUE ( Sheet1[Cost per Occurrence] )
RETURN
Result

@DavieJoe You know it’s Thursday when you are unable to understand a simple requirement as your brain is tired and really looking forward for the weekend :smiley:. Yes, that’s what has happened and I am sorry as I am really unable to understand the new requirement of a measure in a card. It would really help if you could elaborate more on this one.

Thanks.

1 Like

@MudassirAli You’ve got no need to apologise as you’ve been fantastic help in this question (and any others I’ve asked). It’s more likely my shoddy explanation :wink: :rofl:

The Capped Cost works beautifully in my individual table rows so thank you on that. I was just wondering if it is possible for the total of the table to reflect the sum of all the individual rows in the table, instead the total row will follow the logic of the cap and max out at 1000 Occurrences * the Cost per Occurrence but I understand it’s to do with the filter context and the SELECTEDVALUE.

Incident No Total Cost Capped Cost Incident No Total Cost Capped Cost
273280 £58,973 £57,760 273280 £58,973 £57,760
308871 £58,973 £57,760 308871 £58,973 £57,760
280928 £57,760 £57,760 280928 £57,760 £57,760
327789 £57,760 £57,760 327789 £57,760 £57,760
363720 £48,287 £48,287 363720 £48,287 £48,287
Total £281,753 £57,760 Total £281,753 £279,327

I’d want my card measure to calculate the total of all incidents as the above table on the right hand side with a total of £279,327 as per the example and not capping itself at £57,760.

@DavieJoe,

I don’t want to step on the great work that @MudassirAli is doing in this thread, and I’m sure he will provide you with an excellent solution on the above question. However, I thought this post might provide you some good background info on building the necessary evaluation context via virtual tables within measures for card visuals, per your requirement above and potentially give you the ability to solve it yourself:

  • Brian
1 Like

@BrianJ Many thanks, I do need to personally dig a bit further to try and come up with an answer for myself.

@DavieJoe,

Yup, give it a go. It’s a can’t lose strategy - either you solve it yourself, or you don’t - but in the latter case your understanding of @MudassirAli’s solution will be far deeper.

  • Brian
2 Likes

@DavieJoe I believe this is what you are looking for

Card Visual =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Incident No] ),
        "@CorrectTotal", [MeasureR]
    )
RETURN
    SUMX ( vTable, [@CorrectTotal] ) 

Attaching the PBIX file for your reference.
I hope it helps.

eDNA forum – Capped Occurrences Solution.pbix (21.5 KB)

Thanks.

2 Likes

@MudassirAli A big thank you, for some reason I’ve hit a real mental block of late in trying to up my DAX skills. Thanks again for the time you’ve taken to help me.

DJ

So lets see if I understand this properly

VAR vTable is our virtual table
Summarize groups our Incident No.s to distinct values
We create a new “internal measure” called @CorrectTotal and then we drop in our capped measure.
The SUMX then iterates through our virtual table capping each incident according to our capped measure