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,
Total in matrix table is not showing correct, as value is shown through measure. How can i achieve this correct result.
measure is
SalesNProjectsales=
VAR _Currentmnth =MONTH(DATE(YEAR(TODAY(),MNTH(TODAY(),1)-1)
VAR _AMOUNT =IF(MONTH(MAX(dates[Date]))<=_Currentmnth,
[sales amount],
[avg monthly sales])
return
_amount
scenario
We have sales table , dates table , customer table. To calculate the Project sales we have made summarize table of customer and sales, which give us avg monthly sales.
I want to display a matrix chart showing customer and month on axises and amount. iam using measure to display sales amount which is from jan to aug month and pedicted amount which from sep to dec month. Total is not showing correct
Customer | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total |
A | 10 | 10 | 444 | 10 | 10 | 10 | 10 | 2 | 72.29 | 72.29 | 72.29 | 72.29 | 795.1429 |
B | 20 | 222 | 20 | 65 | 20 | 56 | 20 | 20 | 63.29 | 63.29 | 63.29 | 63.29 | 696.1429 |
C | 10 | 10 | 10 | 78 | 10 | 10 | 23 | 10 | 23.00 | 23.00 | 23.00 | 23.00 | 253 |
D | 30 | 3 | 30 | 30 | 30 | 30 | 30 | 54 | 33.86 | 33.86 | 33.86 | 33.86 | 372.4286 |
total | 70 | 245 | 504 | 183 | 70 | 106 | 83 | 86 | 192.4286 | 192.4286 | 192.4286 | 192.4286 |
Thanks
Solved! Go to Solution.
Hi @mb0307 ,
You may create measure like DAX below.
Measure_New=
var _table = SUMMARIZE(Table1, Table1[Customer],"_Value", [SalesNProjectsales] )
return
IF(HASONEVALUE(Table1[Customer]), [SalesNProjectsales], SUMX(_table,[_Value]))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mb0307 ,
You may create measure like DAX below.
Measure_New=
var _table = SUMMARIZE(Table1, Table1[Customer],"_Value", [SalesNProjectsales] )
return
IF(HASONEVALUE(Table1[Customer]), [SalesNProjectsales], SUMX(_table,[_Value]))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mb0307 , Try to change return like
return sumx(values(Table[customer]),_amount)
@mb0307 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |