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.
Hello Power BI experts
I am trying to retreive the SUM of the column highlighted in red that should equate to 52.36.
It's easily done in something like Excel.
The issue I am facing is that you can only use the DAX function SUM on a real coloumn and not on a measure like Productivity. My Productivity measure is :
Productivity = iferror ( SUM([Quantity]) / SUM(vSubmissionFull[AllocatedTime]) * 420 , blank() )
Using SUMX instead only seemed to bypass all the filters so also tried ALLSELECTED with no avail. Thanks in advance !
Solved! Go to Solution.
I got it myself in the end, but with your help.
You were close with the combination of the SUMX and VALUES function. The ALLSELECTED provided the filtered subset of vSubmissionFull that was being imposed on the matrix to get the correct result of 52.36, shown in Grey.
Answer is:
Productivity Total = CALCULATE(SUMX(VALUES(vSubmissionFull[Week Name]),[Productivity]),ALLSELECTED(vSubmissionFull))
@jude_moore,
Create a measure using the DAX below and drag it to Matrix visual, then check if it returns your expected result.
Measure = IF(COUNTROWS(VALUES(vSubmissionFull[DateColumn]))=1, [Productivity],SUMX(VALUES(vSubmissionFull[DateColumn]),[Productivity]))
Regards,
Lydia
Hi Lydia
My Prodcutivity Totay is still being split by Week Name (Text - Reason I used Text was the graph didnt auto format to months / years )
Productivity Total = IF(COUNTROWS(VALUES(vSubmissionFull[Week Name]))=1, [Productivity],SUMX(VALUES(vSubmissionFull[Week Name]),[Productivity]))
@jude_moore,
Create another measure using DAX below.
Measure = CALCULATE([Productivity Total], ALL(vSubmissionFull))
Regards,
Hi Lynda
I tried what you mentioned and if it helps, ive attached the raw data used for this, if it helps !! thanks so far.
prod Grand Total= CALCULATE([Productivity Total], ALL(vSubmissionFull))
https://www.dropbox.com/s/8p4jhod7fmuvwdt/Datafile.xls?dl=0
I got it myself in the end, but with your help.
You were close with the combination of the SUMX and VALUES function. The ALLSELECTED provided the filtered subset of vSubmissionFull that was being imposed on the matrix to get the correct result of 52.36, shown in Grey.
Answer is:
Productivity Total = CALCULATE(SUMX(VALUES(vSubmissionFull[Week Name]),[Productivity]),ALLSELECTED(vSubmissionFull))
Check out this design pattern:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
It deals with a similar issue of summarizing and doing aggregations on measures.
I forgot to mention that I am using DirectQuery with my SSAS and Powerbi on-premis report server so I have some restrictions.
WIll check out that forum
thanks by the way
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 |
---|---|
116 | |
102 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |