How to use Measure in X Axis in chart

[snaruma]

Hi,

I am new to Power BI and will appreciate any help with my question.

So, I have a list of 100 accounts with the following 3 columns:

(1) Normalized Electric Usage per Square footage occupied (Norm_Usage_SFT)

(2) Quartile (Norm/SFT) - this is a derived field where I binned my 100 accounts into 4 quartiles based on their usage in column1

(3) Heating/Cooling Load/Square Footage (Heating_Cooling_ KWH_SFT) for these 100 accounts. The sample dataset is below:

I have created a measure which is a quartile calculation: DynamicQ based on column 3 (Heating_Cooling_ KWH_SFT) which calculates and bins the values in column 3 into 4 buckets. The dataset after adding the measure looks like:

Now, I would like to use this measure (DynamicQ) in the X axis of a bar graph and count the number of accounts. This measure has 4 values: 1, 2, 3, 4 corresponding to the buckets created. I want the output to show something like this: The 4 bars correspond to the 4 values of the measure (1,2,3 and 4) and the counts should represent the number of accounts (count of account_ID).

How can I achieve that? I am unable to do the graph above since a measure is not getting added into the X Axis. I want to add count of account_ids into the Y axiz. Is there any workaround to do that? Any help is appreciated.

Thanks

[Sudhavi_84]

HI Snaruma,

Have you managed to get measures in X Axis? 

I created a measures for dates, and it is working as expected in Table view, But when I select cluster column chart, can not drag dates in to Shared Axis.

Any help>

[v-yulgu-msft]

Hi @snaruma,

Measure is not able to be added to X-axis of chart visual. Instead, you can create a calculated column to get the same results of DynamicQ which can also bins the values in column 3 into 4 buckets. The formula is similar to:

DynamicQ =
IF (
    Table[Heating_Cooling_ KWH_SFT] < 1,
    1,
    IF (
        Table[Heating_Cooling_ KWH_SFT] >= 1
            && Table[Heating_Cooling_ KWH_SFT] < 2,
        2,
        IF (
            Table[Heating_Cooling_ KWH_SFT] >= 2
                && Table[Heating_Cooling_ KWH_SFT] < 3,
            3,
            4
        )
    )
)

Regards,

Yuliana Gu

[snaruma]

Thanks for the response. But I have 2 questions:

(1)  The calculation of the DynamicQ measure is as follows:

DynamicQ =
var FirstQ=PERCENTILEX.INC(allselected(dec_weather),dec_weather[Heating_Cooling_Kwh_SFT],0.25)
var SecondQ=PERCENTILEX.INC(allselected(dec_weather),dec_weather[Heating_Cooling_Kwh_SFT],0.5)
var ThirdQ=PERCENTILEX.INC(allselected(dec_weather),dec_weather[Heating_Cooling_Kwh_SFT],0.75)
VAR ThisVal =selectedvalue(dec_weather[Heating_Cooling_Kwh_SFT])
return
IF (
        ThisVal < FirstQ,
        1,
        IF (
            ThisVal >=FirstQ
                && ThisVal < SecondQ,
            2,
            IF ( ThisVal >= SecondQ && ThisVal < ThirdQ, 3, 4 )
        )
    )

It is different from what you have provided. I can recreate this as a calculated column but it is not dynamic obviously. If I use certain slicers, this column is not recalculating this formula whereas the measure (DynamicQ) is recalculating. And this is what I want , but now I cannot use measure as a slicer or in the bar graph as x-axis. Is there any other way that I can use this measure DynamicQ as a slicer atleast if not in a bar graph?

(1) Calculated columns are not dynamic, correct? If I want the column value to recalculate based on a set of filters, will I be able to do that?

Appreciate any responses.

Thanks!

[Anonymous]

Hi @snaruma ,

I am also facing a similar issue where I want the column value to recalculate based on a set of filters. 

I was curious if you could find any solution to this problem. If yes, could you please share it with us?

Regards!!

[v-yulgu-msft]

Hi @snaruma,

Yes. As you know, measure is not available to be added to slicer nor X-axis of chart. Calculated column is static which won't dynamically changed based on slicer selection.

However, even you want the measure to be dynamical, the category values displayed on X-axis are always 1, 2, 3 and 4. So, there is no difference to use calculated column or measure. I was concerned about what value did you want to dynamically show.

Regards,

Yuliana Gu