cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lucas105 Frequent Visitor
Frequent Visitor

Measure to behave differently on specific level

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. 

 

image.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
lucas105 Frequent Visitor
Frequent Visitor

Re: Measure to behave differently on specific level

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 

 

View solution in original post

2 REPLIES 2
Microsoft v-jayw-msft
Microsoft

Re: Measure to behave differently on specific level

Hi @lucas105 ,

 

Please try the following steps.

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

1.PNG2.PNG

2# Create a calculated column.

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

3.PNG

3# Create a Matrix visual.

4.PNG

 

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 Frequent Visitor
Frequent Visitor

Re: Measure to behave differently on specific level

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 

 

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors