Calculating a dynamic benchmark with multiple conditions
Hi all, I am struggling with a problem and I hope you can help. My star schema looks like this: fact sales, dim product, dim country, dim store I am trying to achieve the following (mind you I have a composite model in my report with 2 DQ connections and I cannot use PowerQuery) I have a measure 'revenue'. Based on this measure I want to calculate a revenue share % for my products against the total revenue. Like: product A 20% product B 14% product C 0.4% This is achieved easily by a metric like: product revenue share % VAR total_revenue_fixed = calculate([revenue], REMOVEFILTERS(dim_product[product_name]) VAR revenue_product = [revenue] RETURN DIVIDE(revenue_product, total_revenue_fixedl) Now I want to add more complexity. When selecting a store from a report slicer, I want to see the product revenue share % for similar stores (say store group). The problem is that I need an average of my original product revenue share % per store group to show up per store id (mapped to a certain store group). So I need an average measure of an existing measure in a new context, which is of course not possible in PBI. I need to make sure that my store slicer(s) do not affect my original 'product revenue share %' and its components (like total revenue). How can I achieve this? I hope it's clear what I am asking.
Average over dates for benchmark
Hi there I am trying to understand the difference in sales pre and post event to see if there is any change. I have payment files for 6 dates, in a single table 'SaleFiles' With columns: SalesValue,SaleName,MembershipDate,DateKey,Customer ID Each customer ID may have multiple sales/refunds on the same day under different types of SaleName. i.e: £10,Membership,02/07/2022,44744,12345 £100,Ticket,02/07/2022,44744,12345 -£20,Refund,02/07/2022,44744,12345 I then have a date table: The dates I have are: 7 May 2022, 4 June 2022, 02 July 2022, 30 July 2022, 27 Aug 2022, 24 Sep 2022 With Columns: Date,DateKey These are joined on the date key. One (DateTable) to Many (SaleFiles) What I want to be able to achieve is, what was the average value of Ticket sales over the 7th May & 4th June combined. Then be able to put this into a table to show the difference between this average and each of the following dates. I would hope to use, DateTable as my date in table or x-axis as this will filter other information elsewhere. I would then also want to be able to drill through by customer to see if the value per customer has increased or decreased on each on after the 4th June. I've tried the following but I can't seem to get it all to stick so I can see the difference. It feels like this should be really simple but I've tweaked all the variations of FILTER | AND | ALL within the measure and for some reason it always ends up with a problem somewhere, either averaging every date seperately (not combining the first two) or returning blanks for any dates outside of the filter date. **UPDATE** I've got this far and the calculation works per customer until I try to display this over a date table. Any help greatly appreciated.Moving Benchmark
Hi All, please I have hit a little snag .. I have a formula that was supposed to calculate the water consumption weekly but the benchmark I want to be flexible depending on the number of persons available in that week. columns : Average headcount week number KPI's one person is assumed to consume a minimum of 1.5 liters weekly NW Benchmark = Calculate(sumx(FASENV,FASENV[headcount]*1.5),filter(FASENV,FASENV[Location] in{("Average Head Count - Staff House I"),("Average Head Count - Staff House II"),("Average Head Count - Staff House III"),("Average Head Count - Staff House IV"),("Average Head Count - Office")})) Kindly assist avanderschilden Anonymous HotChilli Nathaniel_C
