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 need to add only average at only last in matrix chart in power bi.
like above image
Here i calculated average store 1 and 2 devided by 3 (as there are only 3 dates)
when i add average in Values sections in matrix it adds up every columns. I just want at the end of table
i searched google and search some power bi forum but did not hel
Solved! Go to Solution.
Hi, @Anonymous ;
If I understand correct, you could union two tables in excel or in powerbi.
1,union two tables
Table =
VAR _A=SUMMARIZE('PIT',[Date],[SP],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[Date],[SP],[Sum of SLs Validated],"Cate","SL")
RETURN UNION(_A,_B)
2.create two measures.
PIT =
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
SL =
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
3.change label.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
1,union two tables
Table = VAR _A=SUMMARIZE('PIT',[SP],[Date],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[SP],[Date],[Sum of SLs Validated],"Cate","SL")
VAR _C=SUMMARIZE('SL',[SP],"Date","Total","sum",BLANK(),"Cate","SL")
VAR _D=SUMMARIZE('PIT',[SP],"Date","Total","sum",BLANK(),"Cate","PIT")
RETURN UNION(_A,_B,_C,_D)
2.create two measures.
SL =
VAR _a=IF(MAX('Table'[Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
PIT =
VAR _a=IF(MAX([Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
3.change label.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
1,union two tables
Table = VAR _A=SUMMARIZE('PIT',[SP],[Date],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[SP],[Date],[Sum of SLs Validated],"Cate","SL")
VAR _C=SUMMARIZE('SL',[SP],"Date","Total","sum",BLANK(),"Cate","SL")
VAR _D=SUMMARIZE('PIT',[SP],"Date","Total","sum",BLANK(),"Cate","PIT")
RETURN UNION(_A,_B,_C,_D)
2.create two measures.
SL =
VAR _a=IF(MAX('Table'[Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
PIT =
VAR _a=IF(MAX([Date])="Total",CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT")),
CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT")))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
3.change label.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
If I understand correct, you could union two tables in excel or in powerbi.
1,union two tables
Table =
VAR _A=SUMMARIZE('PIT',[Date],[SP],[Total Pits Surveyed],"Cate","PIT")
VAR _B=SUMMARIZE('SL',[Date],[SP],[Sum of SLs Validated],"Cate","SL")
RETURN UNION(_A,_B)
2.create two measures.
PIT =
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="PIT"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="PIT"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
SL =
VAR _a=CALCULATE(SUM([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[Date]=MAX([Date])&&[cate]="SL"))
VAR _b=CALCULATE(AVERAGE([Total Pits Surveyed]),FILTER(ALLSELECTED('Table'),[SP]=MAX([SP])&&[cate]="SL"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
3.change label.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Heaps thanks. all good for now
Hi, @Anonymous ;
If you have slicer, please try it.
1.create two measures:
Store1 =
VAR _a=CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[Date]=MAX([Date])&&[cate]="Store1"))
VAR _b=CALCULATE(AVERAGE([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[cate]="Store1"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
Store1 =
VAR _a=CALCULATE(SUM([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[Date]=MAX([Date])&&[cate]="Store1"))
VAR _b=CALCULATE(AVERAGE([Value]),FILTER(ALLSELECTED('Table'),[Emp]=MAX([Emp])&&[cate]="Store1"))
RETURN IF(HASONEVALUE('Table'[Date]),_a,_b)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Thansk for your quick reply. can you please let me know what is the reason you add cate column in table. i do have data like this
Hi, @Anonymous
I am making simple data based on the pictures you provided, it is naturally better if you can share your files then as well as the results you want to output. so can you share me a .pbix or Complete data and the results you want to output?
if you can access this link then let me know. i have placed excel file to below location
https://www.dropbox.com/scl/fi/xdkziuczp8d83r7m9c7p8/Book2.xlsx?dl=0&rlkey=dbxq8yl9b072gbcqy4j7yizhk
in both tab (Pit and SL) names are same so i had grouped by name in power bi so i do have 4 columns (1) Name (2) Date (3) Pit survey total (4) SL Validated total
And the output like same image above but Store1= Pit and Store2=SL
HI, can you please let me know if you get my file from dropbox and please let me know any outcomes
Thanks
Hi, @Anonymous ;
You can check my last reply record, does it meet your needs?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI, can you please add Total too.
Thanks
i really appriciate your support big thanks, how can i send my files
Hi, @Anonymous ;
You can also use screenshots.
the most important parts are:
1. Sample data as text or screenshots.
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi, @Anonymous ;
You could create a measure .
Method1:
Measure =
var _store1=CALCULATE(AVERAGE([Value]),FILTER(ALLEXCEPT('Table','Table'[Emp]),[cate]="Store1"))
var _store2=CALCULATE(AVERAGE([Value]),FILTER(ALLEXCEPT('Table','Table'[Emp]),[cate]="Store2"))
var _max=CALCULATE(MAX([Date]),ALL('Table'))
return IF(HASONEVALUE('Table'[cate]),SUM([Value]),IF(HASONEVALUE('Table'[Date])&&MAX([Date])=_max,_store1,IF(MAX([Date])<>_max,BLANK(),_store2)))
Then change label
The final output is shown below:
Method2:
1.add rows in power query
2.create a measure.
VAR _store1 =
CALCULATE (
AVERAGE ( [Value] ),
FILTER ( ALLEXCEPT ( 'Table (2)', 'Table (2)'[Emp] ), [cate] = "Store1" )
)
VAR _store2 =
CALCULATE (
AVERAGE ( [Value] ),
FILTER ( ALLEXCEPT ( 'Table (2)', 'Table (2)'[Emp] ), [cate] = "Store2" )
)
RETURN
IF (
MAX ( [cate] ) = "Store1"&& MAX ( [Date] ) = "Average",
_store1,IF (MAX ( [cate] ) = "Store2"&& MAX ( [Date] ) = "Average",_store2,SUM ( [Value] ) ))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
its a amazing help. i will check and update you. really appriciate your support big thanks
if i need to add date slicer then how this measure will be changed automatically as per slicer dates range
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.