Forum Discussion

unsuperuser13's avatar
unsuperuser13
Frequent Visitor
1 year ago
Solved

Weighted Average Not Charting

I am trying to create a line graph with trends by individual markets, and a total weighted average of all markets selected via slicer. I have calculated a weighted average measure using this formula.
 
Weighted Avg = sumx('Table'[Value]*calculate(sum('Table'[Weighting])))/sum(Table'[Weighting])
 
The problem is it appears to be calculating a weighted average for each individual market and plotting it over the respective lines vs showing me a weighted average for all markets. (Of course the weighted average for one item = the item)
 
Am I doing something wrong/is there a workaround for this?
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    The issue is actually somewhere else.  In a standard line chart you can either have a measure and an legend, or multiple measures. You cannot have both.

     

     

  • lbendlin's avatar
    lbendlin
    1 year ago

    You would need to use a custom visual, for example Deneb.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • unsuperuser13's avatar
    unsuperuser13
    Frequent Visitor
    1 year ago

    Here is the structure of my data:

     

    Market Months ActiveAvailableSoldSuccess Rate
    A 410440%
    A 38338%
    A 27229%
    A 16117%
    B 37343%
    B 25240%
    B 13133%
    C 233100%
    C 121.575%
    D 12150%

     

    Here's my process:

     

    Success01234
    Market A0%17%29%38%40%
    Market B0%33%40%43% 
    Market C0%75%100%  
    Market D0%50%   
    Weighted Avg0.0%34.7%46.9%40.3%40.0%
          
    Units Available     
    Market A067810
    Market B03570
    Market C02300
    Market D02000
    Total013151510
          
    Percent Weighting     
    Market A0%46%47%53%100%
    Market B0%23%33%47%0%
    Market C0%15%20%0%0%
    Market D0%15%0%0%0%
          
    % Weight*Success     
    Market A0.0%7.8%13.5%20.3%40.0%
    Market B0.0%7.6%13.3%20.1%0.0%
    Market C0.0%11.5%20.0%0.0%0.0%
    Market D0.0%7.7%0.0%0.0%0.0%
    Weighted Avg0.0%34.7%46.9%40.3%40.0%

     

    Here's my desired output:

     

    I can get everything except the weighted average (on my graph). The weighted average calc I reference in my initial comment seems to calculate for each market individually, so when I graph, it is overlaying a weighted average for each market on top of the individual market lines. I want to be able to graph a total weighted average that adjusts based on slicers and filters.

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      The issue is actually somewhere else.  In a standard line chart you can either have a measure and an legend, or multiple measures. You cannot have both.

       

       

      • unsuperuser13's avatar
        unsuperuser13
        Frequent Visitor
        1 year ago

        Does this mean PowerBI won't allow me to get both on same graph?