cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aasheesh1 Member
Member

Calculated Measure - Multiple IF for Variable Targets

Hi,

 

I'm trying to do a multiple IF function as a calculated field, where I want to state that if a certain value in a slicer / filter is selected, it should then calculate the targets based on the value selected.

 

if i filter on say weekly pipeline created, i would want target to be sum of total target / 52 (weekly)

if i filter on say 30 days pipeline created, i would want target to be sum of total target / 12 (monthly)

 

Can you kindly advise how I can achieve variable targets based on filter.

 

Note that my pipeline data is on one table, targets on another table and the filter for week / month / qtr on anothe table that is linked to the pipeline table (but not to the target table). The target table is linked to the pipeline table though. So I have all the data, just unable to find a way to get it working. :-(

 

Appreciate if anyone can help with this measure at the earliest.

 

Thank you,

Ash

5 REPLIES 5
Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: Calculated Measure - Multiple IF for Variable Targets

HI @aasheesh1

 

Any chance you can share what your tables look like?

 

What would you like to happen if you filter on both weekly or 30-day pipeline?  or is this not possible?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aasheesh1 Member
Member

Re: Calculated Measure - Multiple IF for Variable Targets

Hi @Phil_Seamark,

 

Please find below details as requested.

 

The result should be - If I select a slicer with a pipeline created when? value, it should bring up the value of the related pipeline and the targets for user(s) / product name(s) accordingly.


1. I realise need to find a way of connecting the 3 Created When columns in the pipeline table to the table that maps users and product name to the pipeline created values and also the targets table.

 

2. So if the user selects the slicer with a value as Last 7 days, then the pipeline shown should be last 7 day and target should become sum of all target rows / 52. The same should work if I select the user name or product name (as the targets are classified accordingly)


3. If User selects multiple selections on the pipeline created slicer (for e.g. Last 7 days and Last 30 days), Pipeline should be shown for the last 30 days and target should be for the last 30 Days as well as Last 30 days includes the last 7 days


4. If User does not select anything, pipeline and target is sum of all rows

 

Power BI Tables.JPG

 

aasheesh1 Member
Member

Re: Calculated Measure - Multiple IF for Variable Targets

Hi @Phil_Seamark, Any update on how I can get the result I'm looking for? Appreciate any help on this.

 

Thanks,

Ash

Community Support Team
Community Support Team

Re: Calculated Measure - Multiple IF for Variable Targets

Hi aasheesh1,

 

Are you going to create two slicers on table 'Pipeline Created When Mapping Users' based on column [Created Last 7 Days] and [Created Last 30 Days]? And you have create a relationship between table 'Pipeline Created When Mapping Users' and table 'Pipeline Table', right? So your requirement is like two combine column [Created Last 7 Days] and [Created Last 30 Days] into a single column and then set a slicer based on the combined column, right?

 

You can use Unpivot columns to turn column to rows, click Query Editor-> Transform-> Unpivot Columns, after close&applied, then you can create a measure like formula below, the DAX is just a pattern for your reference because I don't know your specific expected result.

result =
IF (
    SELECTEDVALUE ( Pipeline_Table[Combined_Column] ) = "Created Last 7 Days",
    CALCULATE (
        SUM ( Pipeline_Table[Value USD] ) / 52,
        FILTER ( Pipeline_Table, Pipeline_Table[value] = "Last 7 Days" )
    ),
    IF (
        SELECTEDVALUE ( Pipeline_Table[Combined_Column] ) = "Created Last 30 Days",
        CALCULATE (
            SUM ( Pipeline_Table[Value_USD] ) / 12,
            FILTER ( Pipeline_Table, Pipeline_Table[value] = "Last 30 Days" )
        )
    )
)

Regards,

Jimmy Tao

aasheesh1 Member
Member

Re: Calculated Measure - Multiple IF for Variable Targets

Hi @v-yuta-msft,

 

Thank you for your reply.

 

The slicer I would like to use is from the "unique tables" .. which links to the Pipeline Created When Mapping Users" 

 

Is there a way to link the unique table to the pipeline table without using the unpivot option on the pipeline table? Like by adding a custom column or something else? Unpivot columns increases the number of rows by a lot, which I would want to avoid.

 

Sorry i'm pretty new to this. Appreciate all the help.

 

Thanks,

Ash

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,712 guests
Please welcome our newest community members: