Forum Discussion

MBreden's avatar
MBreden
Helper I
3 years ago
Solved

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...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

v-zhangti's avatar
v-zhangti
Community Support
3 years ago

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's avatar
MBreden
Helper I
3 years ago

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!