Create a Combi Chart as in Excel

[MBreden]

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?

[v-zhangti · Accepted solution]

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.

[MBreden]

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!

[MBreden]

Hi @v-zhangti 

thanks for the measures, this already looks very good 😊

For the axes scaling I will try to create measures.

Best Regards

Melanie