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
mhsk
Helper II
Helper II

Total of Measure doesnt work

Hi,

 

I have a measure that calculates Projected Qty on Hand in warehouse based on Qty and Demand, calculation is all good on item level but i can not crack it how to approach to make a summary of it.

 

My fact table consist of dates of transations only on part level, not all dates.


What is calculated in measure is a date of last transaction and based on that i take the calculation Qty-Demand and then i have Projected Qty, as below it is ok for each item:

mhsk_0-1669890613707.png

 

I have tried the @Greg_Deckler approach with SUMMARIZE but it did not worked here, what i expect to see is to have correct totals and then present data with no part number in matrix/table.

 

Pbix with sample data:  https://1drv.ms/u/s!AoXSL5pl9luJdKfujLoyId2OVDo?e=IVH1xi

 

Any advise 🙂 ?

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @mhsk ,

 

Add the following measure to your model:

Projection Total = 
IF (
    ISINSCOPE ( Extract_ld_det_Items[ld_part] ),
    [Projection QOH],
    SUMX (
        SUMMARIZE (
            ALLSELECTED (
                Extract_ld_det_Items[ld_part],
                Extract_ld_det_Items[Extract_mrp_det (2).Start of Week]
            ),
            Extract_ld_det_Items[ld_part],
            "ProjectionValue", [Projection QOH]
        ),
        [ProjectionValue]
    )
)

MFelix_0-1669892558141.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @mhsk ,

 

Add the following measure to your model:

Projection Total = 
IF (
    ISINSCOPE ( Extract_ld_det_Items[ld_part] ),
    [Projection QOH],
    SUMX (
        SUMMARIZE (
            ALLSELECTED (
                Extract_ld_det_Items[ld_part],
                Extract_ld_det_Items[Extract_mrp_det (2).Start of Week]
            ),
            Extract_ld_det_Items[ld_part],
            "ProjectionValue", [Projection QOH]
        ),
        [ProjectionValue]
    )
)

MFelix_0-1669892558141.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@mfed yes, this code solves my issue totally! Great job!

 

Unfortunatelly i can see i have another issue, my calculation is not as expected on previous step, what i was trying to to is to QOH (QOH_NOW) - Running Total of Demand (SUP-DEM_3) , to calculate Projection each next week.
and again as it works on SUP-DEM_3 level but not if i wrap it up into next measure  Projected QOH

mhsk_0-1669911047623.png

 

have an idea?

So to add a comment, i end up with having a correct date on which i need a measure value but can not get this measure value (so the previous value - Projection QOH)

mhsk_0-1669929933532.png



mh2587
Solution Sage
Solution Sage

Paste your DAX here bro



Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/

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.