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, thanks for all previous help. Love this community.
I've got a bit of an interesting one i'm hoping you guys can help me with.
I've got a calculated colum displaying total month income per customer and because of the datastructure i've been forced into having it like that - I know it is not ideal...
I'm now trying to make a calculated column for a month total based on the sales per month column.
Below is a mock sheet with some data and what would be the desired outcome. the desired outcome is a sum by month of the uniqe value per customer.
desired outcome | |||
date | Customer | Sales per month | Total sales month |
03.01.2022 | xx | 150000 | 450000 |
02.01.2022 | xx | 150000 | 450000 |
01.01.2022 | xx | 150000 | 450000 |
03.01.2022 | yy | 200000 | 450000 |
02.01.2022 | yy | 200000 | 450000 |
01.01.2022 | yy | 200000 | 450000 |
03.01.2022 | zz | 100000 | 450000 |
02.01.2022 | zz | 100000 | 450000 |
03.02.2022 | xx | 50000 | 180000 |
02.02.2022 | xx | 50000 | 180000 |
01.02.2022 | xx | 50000 | 180000 |
03.02.2022 | yy | 100000 | 180000 |
02.02.2022 | yy | 100000 | 180000 |
01.02.2022 | yy | 100000 | 180000 |
03.02.2022 | zz | 30000 | 180000 |
02.02.2022 | zz | 30000 | 180000 |
in advance thanks for any help!
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated column.
Total sales month CC =
VAR _currentmonthend =
EOMONTH ( Data[date], 0 )
VAR _uniquetablepermonth =
SUMMARIZE (
FILTER ( Data, EOMONTH ( Data[date], 0 ) = _currentmonthend ),
Data[Customer],
Data[Sales per month]
)
RETURN
SUMX ( _uniquetablepermonth, Data[Sales per month] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you very much, you my friend are a propper star!
Again thank you as well for also taking the time to show it propperly it is greatly appreciated.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a calculated column.
Total sales month CC =
VAR _currentmonthend =
EOMONTH ( Data[date], 0 )
VAR _uniquetablepermonth =
SUMMARIZE (
FILTER ( Data, EOMONTH ( Data[date], 0 ) = _currentmonthend ),
Data[Customer],
Data[Sales per month]
)
RETURN
SUMX ( _uniquetablepermonth, Data[Sales per month] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |