Forum Discussion

snaruma's avatar
snaruma
Regular Visitor
8 years ago

How to use Measure in X Axis in chart

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 occup...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
snaruma
Regular Visitor
8 years ago

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!

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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!!