Measure to behave differently on specific level

[lucas105]

Hi again, 

I come with another problem I struggle to figure out. 

I prepared a .pbix file with small data set to help figure out, but I can't seem to attach it here so I'll add it in the comment.

Firstly let me explain what I'm trying to do and what's the requirement. 

So basically, I have a restricted attributes in one table - but a new requirement has come out that users should be able to see specific level attributes.

In my sample it's bascially like this: 

Supplier is restricted (or not) based on a 0/1 flag. 

So what I should do is show Dounts (typo 🙂 ) and Random (they are 1) and their respective values and All Others has a 0 flag and sums values for all suppliers with 0. 

I was working with SUMX, which was looking for "All others" in the table and calculated this and for everything else I'd show it based on flag = 1. 

However things get more complex (and I can't seem to figure out) when there's more attributes. 

Category and Type are restricted. Therefore, we want to show suppliers break out as pointed out before inside All category. 

Same goes for Types, I don't want to show specific Types, just All. 

Can you help? Is it even achievable?

Best, 
Luc

[v-jayw-msft]

Hi @lucas105 ,

Please try the following steps.

1# Right-click Category and Type and click New group and create a new group "All".

2# Create a calculated column.

Column = IF('Table'[flag]=0,"Allother",'Table'[Supplier])

3# Create a Matrix visual.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lucas105 · Accepted solution]

I solved this thing quite differently to be honest as I wasn't able to replicate your solution. 

What I did was introduce some new empty rows to my dimension table with specific attirbutes and negative ids I wanted to see (there were 7 of them, so didn't really make a difference) and used something like:

SUMX(FILTER('Dim Table', [id] < 0), 
           CALCULATE(sum([value]), FILTER(ALL'Dim Table', [Column] = SELECTEDVALUE([Column]) && [FLAG] = 1) 
          )

Worked out for me, so really hope that also can help anyone out 🙂

Best, 

Luc