Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I've got data that comes out in a daily format, and I've created a table to summarize my key figure (Min Breaches) into a monthly format:
Location | ABC Indicator | Fiscal Year+Month+Name | L8: Product | Sum Min Breach |
CountryA | C | 2019-P08-Feb | ProductA | 12 |
CountryA | C | 2019-P09-Mar | ProductA | 2 |
CountryA | C | 2019-P10-Apr | ProductA | 0 |
CountryA | C | 2019-P11-May | ProductA | 0 |
CountryA | C | 2019-P12-Jun | ProductA | 10 |
CountryA | C | 2020-P01-Jul | ProductA | 24 |
CountryA | C | 2020-P02-Aug | ProductA | 8 |
CountryA | C | 2020-P03-Sep | ProductA | 30 |
CountryA | C | 2020-P04-Oct | ProductA | 30 |
CountryA | C | 2020-P05-Nov | ProductA | 17 |
CountryA | C | 2020-P06-Dec | ProductA | 19 |
CountryA | C | 2020-P07-Jan | ProductA | 0 |
CountryA | C | 2020-P08-Feb | ProductA | 17 |
CountryA | C | 2020-P09-Mar | ProductA | 23 |
Location | Agreed Min |
CountryA | 5 |
CountryB | 5 |
CountryC | 8 |
CountryD | 5 |
Location | ABC Indicator | Fiscal Year+Month+Name | L8: Product | Sum Min Breach | Min Breach Adj. |
CountryA | C | 2019-P08-Feb | ProductA | 12 | -74 |
CountryA | C | 2019-P09-Mar | ProductA | 2 | -84 |
CountryA | C | 2019-P10-Apr | ProductA | 0 | -86 |
CountryA | C | 2019-P11-May | ProductA | 0 | -86 |
CountryA | C | 2019-P12-Jun | ProductA | 10 | -76 |
CountryA | C | 2020-P01-Jul | ProductA | 24 | -62 |
CountryA | C | 2020-P02-Aug | ProductA | 8 | -78 |
CountryA | C | 2020-P03-Sep | ProductA | 30 | -56 |
CountryA | C | 2020-P04-Oct | ProductA | 30 | -56 |
CountryA | C | 2020-P05-Nov | ProductA | 17 | -69 |
CountryA | C | 2020-P06-Dec | ProductA | 19 | -67 |
CountryA | C | 2020-P07-Jan | ProductA | 0 | -86 |
CountryA | C | 2020-P08-Feb | ProductA | 17 | -69 |
CountryA | C | 2020-P09-Mar | ProductA | 23 | -63 |
Looking to understand what might be causing this. It might just be my calculation that's off, or maybe there is a better way to adjust the monthly data by the agreed min figure.
(Also, the Adj. figure should not go below 0, but that was going to be the next step)
Thank you!
Solved! Go to Solution.
Hi,
I think you can try this more pretty formula:
Min Breach Adj. =
VAR a =
'HistoricMonthly'[Sum Min Breach] - CALCULATE ( SUM ( Locations[Agreed Min] ) )
RETURN
IF ( a < 0, 0, a )
Best Regards,
Giotto
ooookay, so I'm an idiot and think I've just figured it out using:
Which is good to go!
Min Breach Adj. = MIN('IHistoricMonthly'[Sum Min Breach],MAX(0,'HistoricMonthly'[Sum Min Breach]-calculate(SUM(Locations[Agreed Min]))
Hi,
I think you can try this more pretty formula:
Min Breach Adj. =
VAR a =
'HistoricMonthly'[Sum Min Breach] - CALCULATE ( SUM ( Locations[Agreed Min] ) )
RETURN
IF ( a < 0, 0, a )
Best Regards,
Giotto
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |