Forum Discussion

Frequent Visitor

1 year ago

Solved

Categorical Calculated Measure on X Axis

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 anot...

v-sdhruv
1 year ago
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!

pankajnamekar25

Icon for Super User rank

Super User

1 year ago

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.