Approximating benchmark line in combo chart

I have a combo chart with 2 axis.
On the bar axis, I have total score, and on the line chart, I have total sales.
I added a "benchmark" value (85%) to the grouped bar chart, but the user wants the benchmark to be a line, not a bunch of bars. I can't add a benchmark line to a combo chart, per this link, so I'm stuck trying to find another way My thought was to approximate the benchmark value of the percentage axis by getting 85% of the max value of the totalInvoicedYTD axis.

However, I'm having trouble building a measure to do that, which will work in this combo chart. I have a measure I can put in a card to get the highest value of the totalInvoicedYTD amount by vendor (Code below), but when I try to readd it back to the combo visual, I get this error:

Goal:
Orange Benchmark line

Code of measure:

maxInvoicedItemsYTD = 
VAR yearBeginning =
    DATE ( YEAR ( MIN ( 'Date'[Date] ) ), 1, 1 )
VAR periodEnd =
    MAX ( 'Date'[Date] )
VAR InvoiceTotal =
MAXX(
    SUMMARIZECOLUMNS(
        Vendors[No],
        FILTER(
            ALL ( PurchaseInvoiceLine ),
            PurchaseInvoiceLine[PostingDate] >= yearBeginning
            &&PurchaseInvoiceLine[PostingDate] <= periodEnd
        ),
        FILTER(Vendors,Vendors[WTE Scorecard]=1),
        "TotalInvoiced",
        COALESCE ( SUM ( 'PurchaseInvoiceLine'[Amount] ), 0 )
    ),[TotalInvoiced]
)
return InvoiceTotal

Link to sample file:
https://ufile.io/k6e7ox0b

Need Help

Anonymous

2 years ago

I was able to figure out a measure applying the max sum by vendor to to each vendor:

MaxAmountByVendor = 
VAR yearBeginning = DATE(YEAR(MIN('PurchaseInvoiceLine'[PostingDate])), 1, 1)
VAR periodEnd = MAX('Date'[Date])
var maxamount = 
MAXX(
    GROUPBY(
        FILTER(
            ALL('PurchaseInvoiceLine'),
            'PurchaseInvoiceLine'[PostingDate] >= yearBeginning && 'PurchaseInvoiceLine'[PostingDate] <= periodEnd
        ),
        'PurchaseInvoiceLine'[VendorNo],
        "MaxAmount", SUMX(CURRENTGROUP(),'PurchaseInvoiceLine'[Amount])
    ),[MaxAmount]
)
return maxamount

4 Replies

Daniel29195
Community Champion
2 years ago
Anonymous
output :

you can try this :

in the upper and lowerbound you drag and drop the column : benchmark 85% .

you can even change its color .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
- Daniel29195
  Community Champion
  2 years ago
  Anonymous
  
  forgot to mention the most important thing :
  change the color of the bar to white , or you can simply put transparency to 100%
  
  If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution✅!
  It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
  - Anonymous
    Not applicable
    2 years ago
    Hi Daniel,
    
    It ended up looking like this:
    
    I was able to solve it though - I put my solution below. Thank you for your help.

Anonymous

Not applicable

2 years ago

I was able to figure out a measure applying the max sum by vendor to to each vendor:

MaxAmountByVendor = 
VAR yearBeginning = DATE(YEAR(MIN('PurchaseInvoiceLine'[PostingDate])), 1, 1)
VAR periodEnd = MAX('Date'[Date])
var maxamount = 
MAXX(
    GROUPBY(
        FILTER(
            ALL('PurchaseInvoiceLine'),
            'PurchaseInvoiceLine'[PostingDate] >= yearBeginning && 'PurchaseInvoiceLine'[PostingDate] <= periodEnd
        ),
        'PurchaseInvoiceLine'[VendorNo],
        "MaxAmount", SUMX(CURRENTGROUP(),'PurchaseInvoiceLine'[Amount])
    ),[MaxAmount]
)
return maxamount

Forum Discussion

Approximating benchmark line in combo chart

4 Replies

Related content

KQL Database Benchmarks

Benchmark line

Data benchmarking

Benchmark line in combo visual

approximate match lookupvalue