Forum Discussion
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 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.
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!
6 Replies
- pankajnamekar25
Super User
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
v-sdhruvCommunity Support
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!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.
- 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
