Forum Discussion

Emmy66's avatar
Emmy66
Helper V
5 years ago

Create a table to hold 2 Measures

Hi,

I'm trying to create a clustered bar chart where I have 10 measures. My intention is to have 2 of the measures grouped or stored in a table so that there will be spaces between each pair.

currently my bar chart looks like this:

 

 

Expected output for the bar chart

 

 

Each purple and green bar represents a measure. I'm trying to replicate this chart in PBI as it was originally created in excel.

 

Metrics_Charts_OM_Logon =
UNION
(SELECTCOLUMNS('Key Metrics OM',"YourSelection",[% All_Users_w_login_activity]),
SELECTCOLUMNS('Key Metrics Partner OM',"Firm",[% Partners_Only_OM_Logon])
)
I've tried doing a union to combine 2 measures but unfortunately I couldn't go any further. My intention is for Metrics_Charts_OM_Logon to be held in a table which I can use to plot the chart. Is just an idea but maybe someone might have a better approach. Your assistance on this will be appreciated.
Need Help

6 Replies

  • fhill's avatar
    fhill
    Resident Rockstar
    5 years ago

    Try this useful trick...  Most Charts in Power BI allow you to assign Multiple VALUES.  Each of these Values will group together simular to what you were looking to make...  

     

    Create a Measure of one set of bars (let's say the Purple Lines in your example)

    Gropu A = CALCULATE(SUM('Table (2)'[Values]), FILTER('Table (2)', 'Table (2)'[Group] = "A"))
    Then create a Second Measure of the other set of Bars (let's say the Green Lines in your example)
    Gropu B = CALCULATE(SUM('Table (2)'[Values]), FILTER('Table (2)', 'Table (2)'[Group] = "B"))
     
    Here's some sample data I put together, remember BOTH sets of lines have to SHARE a vertical Axis in common.  In this example, we are Grouping (by Color) Group = A or Group = B, but the Shared Axis values have to be in common as well.
    And here's the set-up with BOTH Measures in the Values.

     

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      Hi fhill , thanks for your response. Very much appreciated. What you illustrated is exactly what I intend to achieve. Like you mentioned, that I should create a measure of one set of bars (how do I accomplish that please?) Currently I have 10 measures that are dynamically calculated (all expressed in %) which I've created e.g. the measures are based on the calculation below.

       

      I have 6 other measures calculated in same manner. The measures with prefix Partners_Only (represents the purple bar) and the prefix All_users (represents the green bar).  With the above measures, how do I implement your suggestion please bearing in mind that the measures are not held in any table but calculated on the fly?
      • fhill's avatar
        fhill
        Resident Rockstar
        5 years ago

        In my example, the 2 Measures have filters of the source data that allow both sets of data to be displayed on the same chart without overlap (Legend). 

         

        It looks like your example measures are DIVIDING other measures?  You'll need to post an example of your source data for us to help you with the actual Measure code.  

         

         

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      Hi amitchandak , thanks for your reply. I'm not looking for a measure slicer. fhill's response is what I intend to achieve. I hope it will guide you to understand my expected output.