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 ,
I have below dataset
KPI | Actual Value | Program | Region |
Material | 80 | A | South |
Material | 70 | B | South |
Material | 70 | C | South |
Number of Sites | 1 | A | South |
Number of Sites | 2 | B | South |
Number of Sites | 3 | C | South |
Total Sites = Σ Material* Number of Sites =SUM(80*1+70*2+70*3)
I want to create Total Sites Measure, not by calculated columns
Thanks
Solved! Go to Solution.
@Anonymous I beleive this is the measure you want:
Total Sites =
sumx(values('Table'[Program]),
CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Material")
* CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Number of Sites"))
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@Anonymous I beleive this is the measure you want:
Total Sites =
sumx(values('Table'[Program]),
CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Material")
* CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Number of Sites"))
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Correct. I m looking for this one
@Anonymous - Seems like:
Measure =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table',[Region],[Program],"Value",SUMX(FILTER('Table',[KPI]="Material"),[Actual Value]),"Number",SUMX(FILTER('Table',[KPI]="Number of Sites"),[Actual Value])),
"Product",[Value] * [Number]
)
RETURN
SUMX(__Table,[Product])
But, I get the feeling that you shouldn't have unpivoted your KPI column...
@Anonymous , pivot this data and try
https://radacad.com/pivot-and-unpivot-with-power-bi
https://youtu.be/oKByyI09Bno
then you can multiple
Or try a measure like
sumx(summarize(Table, Table[Program],Table[Region], "_1", calculate(sum(Table[Actual Value]), Table[KPI] ="Material" ) * calculate(sum(Table[Actual Value]), Table[KPI] ="Number of Sites" )),[_1])
My requirement is to do without Pivot data.Is there any method do without pivoting?
@Anonymous , try a measure like
sumx(summarize(Table, Table[Program],Table[Region], "_1", calculate(sum(Table[Actual Value]), Table[KPI] ="Material" ) * calculate(sum(Table[Actual Value]), Table[KPI] ="Number of Sites" )),[_1])
getting this error
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |