Categorical Calculated Measure on X Axis

[Arash_Bhz]

Hi,

I have a big data model with several fact and dimension tables in PowerBI I am trying to create a chart that shows inventory coverage Bins, which is a calculated measure that categorizes another calculated measure showing inventory coverage, into 5 bins. I want to use these five bins as x axes of charts, for example, a combination chart showing the count of Material and sum of inventory at selected time, which also takes into account the filtering context, like region, other slicers, etc. PowerBI does not let me use a calculated measure(Inv Coverage Bins) on the x-axis. how should I circumvent this issue to get the visual I want? (Note: I can not create a calculated column instead, since the measure is complex and based on aggregation on several fact tables.)

Thanks.

[v-sdhruv]

Hi @Arash_Bhz ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Arash_Bhz ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Arash_Bhz ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @Arash_Bhz ,
You can try using -

Inventory By Bin =
SWITCH(
SELECTEDVALUE('InvCoverageBins'[Bin]),
"0-5 Days", CALCULATE([Inventory], FILTER(ALL('Materials'), [InvCoverageMeasure] >= 0 && [InvCoverageMeasure] < 5)),
"5-10 Days", CALCULATE([Inventory], FILTER(ALL('Materials'), [InvCoverageMeasure] >= 5 && [InvCoverageMeasure] < 10)),
"10-20 Days", CALCULATE([Inventory], FILTER(ALL('Materials'), [InvCoverageMeasure] >= 10 && [InvCoverageMeasure] < 20)),
"20-30 Days", CALCULATE([Inventory], FILTER(ALL('Materials'), [InvCoverageMeasure] >= 20 && [InvCoverageMeasure] < 30)),
"30+ Days", CALCULATE([Inventory], FILTER(ALL('Materials'), [InvCoverageMeasure] >= 30))
)

Material Count By Bin =
SWITCH(
SELECTEDVALUE('InvCoverageBins'[Bin]),
"0-5 Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), FILTER(ALL('Materials'), [InvCoverageMeasure] >= 0 && [InvCoverageMeasure] < 5)),
"5-10 Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), FILTER(ALL('Materials'), [InvCoverageMeasure] >= 5 && [InvCoverageMeasure] < 10)),
"10-20 Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), FILTER(ALL('Materials'), [InvCoverageMeasure] >= 10 && [InvCoverageMeasure] < 20)),
"20-30 Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), FILTER(ALL('Materials'), [InvCoverageMeasure] >= 20 && [InvCoverageMeasure] < 30)),
"30+ Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), FILTER(ALL('Materials'), [InvCoverageMeasure] >= 30))
)

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[Arash_Bhz]

Thanks, this was helpful, but still there is a bug, I can not use a measure inside the calculate function within the switch function, it give me error, since InvCoverageMeasure is itself a calculated measure as well, that I used in a table with materials in rows to get the coverage for each material.

[pankajnamekar25]

Create a Disconnected Bins Table (Manually or DAX)

This is your axis table

InvCoverageBins = DATATABLE(

    "Bin", STRING,

    {

        {"0-5 Days"},

        {"5-10 Days"},

        {"10-20 Days"},

        {"20-30 Days"},

        {"30+ Days"}

    }

)

Inventory By Bin =

VAR SelectedBin = SELECTEDVALUE(InvCoverageBins[Bin])

RETURN

SWITCH(

    TRUE(),

    SelectedBin = "0-5 Days", CALCULATE([Inventory], [InvCoverageMeasure] >= 0 && [InvCoverageMeasure] < 5),

    SelectedBin = "5-10 Days", CALCULATE([Inventory], [InvCoverageMeasure] >= 5 && [InvCoverageMeasure] < 10),

    SelectedBin = "10-20 Days", CALCULATE([Inventory], [InvCoverageMeasure] >= 10 && [InvCoverageMeasure] < 20),

    SelectedBin = "20-30 Days", CALCULATE([Inventory], [InvCoverageMeasure] >= 20 && [InvCoverageMeasure] < 30),

    SelectedBin = "30+ Days", CALCULATE([Inventory], [InvCoverageMeasure] >= 30)

)

Material Count By Bin =

VAR SelectedBin = SELECTEDVALUE(InvCoverageBins[Bin])

RETURN

SWITCH(

    TRUE(),

    SelectedBin = "0-5 Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), [InvCoverageMeasure] >= 0 && [InvCoverageMeasure] < 5),

    SelectedBin = "5-10 Days", CALCULATE(DISTINCTCOUNT(Materials[Material ID]), [InvCoverageMeasure] >= 5 && [InvCoverageMeasure] < 10),

    ...

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.