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, this is my monthly data ( Month as number 1, 2,3 .. )
Month | ||||||||||||||
BLOCK | Global | Mgmt | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
INDIA | Overheads | Cola | - 0.28 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 |
INDIA | Overheads | Pepsi | 0.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
JAPAN | Overheads | Cola | - 0.07 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 |
JAPAN | Overheads | Pepsi | - 0.02 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 | 0.06 |
USA | Under | Cola | - 0.00 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 |
USA | under | Lime | 0.00 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 |
What I am struggling and my requiremnt is..
If I select India, Overhead and Pepsi with
Month 1 : My value should come -0.28
Month 2 : -0.13
MOnth 3: 0.02
i.e Cummulative addition based on the Block , Global , Mgmt and month number filter.
Solved! Go to Solution.
Hi @Anonymous ,
After unpivoting your table, you could create a new column to show the data.
Column =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Attribute] <= EARLIER ( 'Table'[Attribute] )
&& 'Table'[Mgmt ] = EARLIER ( 'Table'[Mgmt ] )
&& 'Table'[BLOCK] = EARLIER ( 'Table'[BLOCK] )
)
)
Here is my test file for your reference.
Hi @Anonymous ,
After unpivoting your table, you could create a new column to show the data.
Column =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Attribute] <= EARLIER ( 'Table'[Attribute] )
&& 'Table'[Mgmt ] = EARLIER ( 'Table'[Mgmt ] )
&& 'Table'[BLOCK] = EARLIER ( 'Table'[BLOCK] )
)
)
Here is my test file for your reference.
thank you all of you. This has resolved my problem.
Hi,
you can do it like this:
REgrads FrankAT
90% correct, I did the same way but why if I take the monthe number as a seperate slicer/filter and If I select 2, the output is not coming as -0.13 or If I select 3 the output is not coming as 0.02.
Hi @Anonymous,
try this:
or change the slicer properties like this:
Regards FrankAT
If you are using date the you can use following for cumulative
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
If you have month No,
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(Table),Table[Month] <=maxx(Table,Table[Month])))
That will work with year.
hi,
the output is not as expecetd if I select the filters like Block , Global and MGT. The cummulative is working fine based on the month no .
If you have date, prefer the formula In given with date using date calendar. If you can create date using month and year do that.
If not, move month to another table and then use all(Month) in the filter (all(month), month[month]<=max(month[month]))
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |