Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I found the solution by creating a new table and group the monthnyear column and then using the relationship I evaluate CALCULATE (sum[SALES[ordervalue])) in a new calculated column. And then I bring in this new created value by RELATED to my sales data. Then by using simple formula called MAX, i have done it.
However,
I'm struggling to find a solution for a calculation that i am trying to do in a measure.
I am trying to repeat the sum of each months sales value on each date in a table.
I have tried cumulative totals using MTD, ALLSELECTED, TOTALMTD etc but I could not get the solution.
Please see below table,
I hope i could explaing my situation,
mant thanks for your replys
Cheers!
Dates
01/01/2020 - 100 - 800
02/01/2020 - 200 - 800
03/01/2020 - 300 - 800
04/01/2020 - 100 - 800
.
.
.
31/01/2020 - 100 - 800
01/02/2020 - 100 - 350
02/02/2020 - 150 - 350
.
.
.
29/02/2020 - 100 - 350
.
.
.
31/12/2020....
Solved! Go to Solution.
Hi @Molcy ,
We can create a measure to meet your requirement:
MonthTotal =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date].[MonthNo] = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) )
&& 'Table'[Date].[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
)
)
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.
Best regards,
Hi @Molcy ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @Molcy ,
We can create a measure to meet your requirement:
MonthTotal =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date].[MonthNo] = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) )
&& 'Table'[Date].[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
)
)
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.
Best regards,
You are the best 🙂
create the following column
stmonth = STARTOFMONTH(table[date])
This month sales = sumx(filter(table,table[stmonth]=earlier(table[stmonth])),table[sales])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi
I created a column in my sales table using below;
Stmonth = STARTOFMONTH(Siparis_Data[SiparisTarihi])
And then i created the following measure:
The second one is also a column. If you need a measure then try allexcept. Also prefer a date dim in that case
This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(Siparis_Data[stmonth]))
This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(date[stmonth]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Thanks for quick reply.
I have a calander table which work just fine.
This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(Siparis_Data[stmonth])) or;
This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(date[stmonth])) works both fine.
This formula returns total sales for each row. I mean Grand Total sales.
However this is not I am trying to do. What i am trying to do is;
If January total sales is 150.000 for instance, then I want each dates in January repeat 150.000,
If February total sales is 200.000 for instance, then I want each dates in February repeat 200.000,
so on and so forth....
Thanks
Refer : https://community.powerbi.com/t5/Desktop/calculate-category-total-in-table-visualization/td-p/519340
Or you can use quick measures
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |