Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic slicers mapping of target/ budget with actual

Dear All,

 

I am unable to map data from two separate files into the report (screenshot below) based on sllcer selection -dummy file link mentioned for ref : 

  1. Target/ Budget nos. (table format)
  2. Actual (Report created from data table- using measures)
    • this report (screenshot below ) is ready and calculating correctly, but unable to mapp the target with this report.

File link mentioned  - Dummy file link  

 

Please advise how to calculate the same/ what action to be taken.

Thanks and regards,

Pratz

  1. Actual s based on slicer selectionActual s based on slicer selectionTarget/ budget fileTarget/ budget file
5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @Anonymous,

After a check on your sample file, I found your nested many measure formulas with different filters, it may cause the filter confilt.
In addition, it looks like you enable the all function in your formula with will ignore all filter effect, please use allselected function to instead.

Optimizing nested iterators in DAX 

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thank you @Xiaoxin Sheng. 

Apologies for sharing an incorrect measure, which i forgot to delete.

As suggested, have incorporated only the relevant ones.

 

Please advise on the mapping of target/ budget with Actuals.

 

attaching the updated file for refDummy File 

 

Regards,

Pratz

 

HI @Anonymous,

You can try to use the following measure formulas to replace your raw measures if they meet for your requirement:

N_YTD Exits =
VAR currDate =
    MAX ( base[Date] )
RETURN
    CALCULATE (
        [Exits],
        FILTER (
            ALLSELECTED ( base ),
            YEAR ( base[Date] ) = YEAR ( currDate )
                && MONTH ( base[Date] ) = MONTH ( currDate )
                && base[Date] <= currDate
        )
    )

N_YTD_AVG_HC =
VAR currDate =
    MAX ( base[Date] )
RETURN
    CALCULATE (
        [MTD AVG HC],
        FILTER (
            ALLSELECTED ( base ),
            YEAR ( base[Date] ) = YEAR ( currDate )
                && MONTH ( base[Date] ) = MONTH ( currDate )
                && base[Date] <= currDate
        )
    )

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft 

Hi Xiaoxin Sheng,

Thank You for your time.

My MTD and YTD avg HC is already sorted, no issue with that.

I just want a solution/ or advice to get the data from 2 other tables (target %and Budgeted nos.) into the table on BI, basis the selection of 3 slicers - for a single selection and also all the 3 different slicers selected together .

 

However, i am unable to achieve the desired outcome.

Regards,

Pratz.

Hi @Anonymous,

So you mean your records are stored in different tables and not similar to the dummy sample you shared? If this is a case, maybe you can try to create a calculated table to union table and add a column to remark the source table.

Merged =
UNION (
    ADDCOLUMNS ( Table1, "Source", "Table1" ),
    ADDCOLUMNS ( Table2, "Source", "Table2" ),
    ADDCOLUMNS ( Table2, "Source", "Table3" )
)

After these steps, you can modify the measure formula to calculate on merge table and add a condition with source field to prevent the calculation across different tables.
Regards,

Xiaoxin Sheng

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.