Forum Discussion
Create a Combi Chart as in Excel
Hi all,
I have a simple data table with retailers, categories and values.
Now I want to display the relative ratio of retailers in each category by selecting a category.
The calculated values in a matrix are correct.
In a combination chart, a line is needed for the average.
I can do this in Excel, but fail in Power BI ☹️
Can anyone help me?
Hi, MBreden
You can try the following methods.
Measure = Var _N1=[TT Sum] Var _N2=CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),[Retailer]=SELECTEDVALUE(DimRetailer[Retailer]))) Return DIVIDE(_N1,_N2)Average = Var _Sum1=CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),[Category]=MAX(DimCategory[Category]))) Var _Sum2=CALCULATE(SUM(Data[Value]),ALL(Data)) Return DIVIDE(_Sum1,_Sum2)Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi, MBreden
You can try the following methods.
Measure = Var _N1=[TT Sum] Var _N2=CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),[Retailer]=SELECTEDVALUE(DimRetailer[Retailer]))) Return DIVIDE(_N1,_N2)Average = Var _Sum1=CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),[Category]=MAX(DimCategory[Category]))) Var _Sum2=CALCULATE(SUM(Data[Value]),ALL(Data)) Return DIVIDE(_Sum1,_Sum2)Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-zhangti ,
I managed to add the measurements to my original file.
As the values in the categories are very different, I need the maximum of the retailers in the selected category to scale the Y-axis.
I hope you or another DAX professional can help me with the calculation?
Many thanks!
