cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Apply total value not to every coloumn

Hey guys,

 

do you know maybe if it is possible to decide on what coloumn to apply the total value? I have 3 measures (they have to stay measure, cannot change to coloumn because they are linked to a dynamic filter).

 

I would like to know if it is possible not to show for the BU Status?

 

Many thanks in advance,

 

Adam

 

apply total_not_to_every_coloumn.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Apply total value not to every coloumn

@Anonymous

 

Something like this ?

BU Status =
IF (
    ISFILTERED ( 'Incident Report'[Bus Unit] );   
UNICHAR ( IF ( 'Incident Report'[Measure2 as Text] = "0"; 10003; IF ( 'Incident Report'[Measure2 as Text] = "2"; 10007; IF ( 'Incident Report'[Measure2 as Text] = "1"; 33 ) ) ) ) )

where we're only adding the IF with the check  (in red) to what you already had.

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Apply total value not to every coloumn

Hi @Anonymous

 

Yes, within your measures, you can tell whether you are at the Grand total row by testing with something like:

ISFILTERED(Table1[Bus Unit])

 

and then returning blank in that case (or any other thing you want the mesure to return).

 

Anonymous
Not applicable

Re: Apply total value not to every coloumn

hey @AlB,

 

thank you for your fast reply. 

 

could you please help me, where to put it? I have a measure, that is actually converting a measure into Unichar:

 

BU Status = UNICHAR(IF('Incident Report'[Measure2 as Text]="0";10003;IF('Incident Report'[Measure2 as Text]="2";10007;IF('Incident Report'[Measure2 as Text]="1";33))))
 
Measure2 as Text = FORMAT( CALCULATE(MAX('Incident Report'[ASPStatusID]);ALLEXCEPT('ServiceDashboard'; 'ServiceDashboard'[Bus Unit]);
FILTER('Incident Report';(CALCULATE(MIN('Incident Report'[Incident Start Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident Start Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)||CALCULATE(MIN('Incident Report'[Incident End Date]))>=CALCULATE(MIN('CALENDAR'[Date])) && CALCULATE(MAX('Incident Report'[Incident End Date]))<=CALCULATE(MAX('CALENDAR'[Date])+1)))
)+0;"")
 
Thank you in advance!
 
Many thanks,
 
Adam
Super User
Super User

Re: Apply total value not to every coloumn

@Anonymous

 

Something like this ?

BU Status =
IF (
    ISFILTERED ( 'Incident Report'[Bus Unit] );   
UNICHAR ( IF ( 'Incident Report'[Measure2 as Text] = "0"; 10003; IF ( 'Incident Report'[Measure2 as Text] = "2"; 10007; IF ( 'Incident Report'[Measure2 as Text] = "1"; 33 ) ) ) ) )

where we're only adding the IF with the check  (in red) to what you already had.

View solution in original post

Community Support Team
Community Support Team

Re: Apply total value not to every coloumn

Hi @Anonymous,

 

I'd like to suggest you take a look at following blog to know how to use condition to confirm current hierarchy level,  then you can simply use if statement with that condition to replace specific level result.

Clever Hierarchy Handling in DAX

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Anonymous
Not applicable

Re: Apply total value not to every coloumn

Hey @AlB,

 

I checked and it's unfortunately not working. :/ However, for the ISFILTERED I put ServiceDashboard[Bus Unit], as it's only present in that database.

 

Maybe some other idea? Smiley Happy I would greatly appreciate!

 

Many thanks,

 

Adam

 

BU_Status_answer.JPG

Super User
Super User

Re: Apply total value not to every coloumn

@Anonymous

 

What's happening exactly? What is not working?

Can you share the pbix? We'd be faster like that

Anonymous
Not applicable

Re: Apply total value not to every coloumn

Hey @AlB,

 

sorry, I just had to re-create the table, afterwards it worked! Many thanks for it!

 

Do you know maybe, how it is possible to add a measure as a filter? - a workaround maybe?

 

The "Current Service Status" Measure is linked to the slicer, and changing based on the time.

 

The "Current Service Status Static" Coloumn is also linked to slicer, however not changing based on the time.

 

I would like to have however a chart, where I have an overview of the service statuses, within the chosen time horizon (Measure as Filter).

 

Any help would be greatly appreciated, here is the link for the pbtx. https://1drv.ms/u/s!Ah2VGOQRBzRVhijIn6jmykLe5DBS

 

Many thanks,

 

Adam

Super User
Super User

Re: Apply total value not to every coloumn

@Anonymous

You can use measures in the visual level filters

Anonymous
Not applicable

Re: Apply total value not to every coloumn

Hey @AlB,

 

it is somehow not working. The issue is the following: for the total period that is shown on the slicer 01/01/2018 till 31/12/2018 everything shows correct info (current service status as measure & current service status static as coloumn). Based on Current service static I could create the pie chart (based on current service status was not able).

 

The moment I change the time period, for example to 01/01/2018 till 05/11/2018 everything that is a measure is changing properly. However, the pie chart that is based on current service status static is not changing. 

 

See the attachments Smiley Happy

 

My aim would be, that this is also chaning dynamically with the slicer. However, since this is not a measure I'm not sure how.. Even, when I put the measure to the visual level filter it's not correct, I think it should be the legend (not allowed).

 

Many thanks,

 

AdamOriginal.JPGChanged.JPG

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 44 members 826 guests
Please welcome our newest community members: