How to show total of a measure

[QQB]

Hello, I am new to Dax and hope someone can help me with a measure total question, Thank you so much in advance!

In the 'Fact Log Results' table, I have a measure to obtain monthly targets with  values from another table 'Instrument Events' based on some conditions. 

PM_Monthly_Target_2  =

VAR JP = SELECTEDVALUE('Fact Log Results'[Job Plan Number])

VAR Instr = SELECTEDVALUE('Fact Log Results'[Instrument])

VAR Target =

CALCULATE(

   VALUE ('Instrument Events'[PM Monthly Target]),  

    'Instrument Events'[Job Plan Template] =JP,

    'Instrument Events'[Instrument] =Instr

)

RETURN

IF(

    NOT ISBLANK(JP),

    Target

)

Then I created another measure based on the previous measure :

PM_Monthly_Target_4 =

CALCULATE(

   [PM_Monthly_Target_2],

    DATESYTD('Calendar'[Date])

)

Then I have the PM Completion% measure calculated based on the PM_Monthly_Target_4 and # of PMs Performed_2

PM Completion% =

 var comp=IFERROR(  [# of PMs Performed_2]/[PM_Monthly_Target_4], "")

 RETURN

 if (comp>1, 1, comp)

The question is how can I have the PM_Monthly_Target_4 show Total Value in the table below? and the Total of PM Completion% would be based on the total of PM_Monthly_Target_4 and total of # of PMs Performed_2?

[Bibiano_Geraldo]

Hi @QQB , 
The problem raise in PM_Monthly_Target_2 measure, because its only return total where JP  is not blank, so in the total rows there's no jp, thats why even PM_Monthly_Target_2 column return blank, and this will affect all other measures that depend on this one.

To bypass this, i'll recommend you to filter a table with your conditions directly in your calculate target, something like this:

PM_Monthly_Target_2 = 
VAR JP = SELECTEDVALUE('Fact Log Results'[Job Plan Number])
VAR Instr = SELECTEDVALUE('Fact Log Results'[Instrument])
VAR Target =
CALCULATE(
   VALUE ('Instrument Events'[PM Monthly Target]),  
    'Instrument Events'[Job Plan Template] =JP,
    'Instrument Events'[Instrument] =Instr,
    FILTER(
        Fact Log Results,
        NOT ISBLANK('Fact Log Results'[Job Plan Number])
    )
)
RETURN
Target

This approach will ensure that where Job plan is blank will be not calculated, and this may automatically correct all your other measures. 

Let me know if its all ok.

[QQB]

Thank you so much @Bibiano_Geraldo ! You suggestion is a step closer to what I try to achieve but still need some help if you can provide🙏

https://1drv.ms/u/s!AimJkmtFxzam1AB9JvsYvXryypfP 

Basically the Job Plan Number tell me how many job we need to perform, 1W means 4 jobs to do in a month. So the monthly target should be 4 as shown in PM_Monthly_Target_4 .  # of PMs Performed_2 tells me how many were actually done. 

What I try to achieve here is to get a target total by Month (and add them together to get the yearly total), then calculate the completion rate by month (and by year as well). 

As you have mentioned, my problem is that when the Job plan wasn't performed in certian months, the target were not added to the total..., how can I fix this ?  I also created the following measure but the total still don't capture the target numbers that no results entered.   The screenshots show numbers I expect. 

PM_Monthly_Target by MY =

IF( isblank( sumX( SUMMARIZE('Fact Log Results', 'Fact Log Results'[Instrument], 'Fact Log Results'[Job Plan Number],'Calendar'[Month-Year]),

    CALCULATE(

   [PM_Monthly_Target_2],

    DATESYTD('Calendar'[Date])))),

  [PM_Monthly_Target_4],

    sumX( SUMMARIZE('Fact Log Results', 'Fact Log Results'[Instrument], 'Fact Log Results'[Job Plan               Number],'Calendar'[Month-Year]),

    CALCULATE(

   [PM_Monthly_Target_4],

    DATESYTD('Calendar'[Date]))))

[Bibiano_Geraldo]

Hi @QQB ,

Happy that we're close to resolve your problem, but i'll ask you for sample file to interact with this problem, just to give you the right way for your goal, its not easy just suppose the result.

But, take a note to not share a sensitive information, you can make a fake dataset and reproduce the problem and uploado the pbix to onedrive or other cloud and share the link here in comments.

Thank you.

[QQB]

I created this fake file and hope it works... Thank you sooo much for your help!

https://1drv.ms/u/s!AimJkmtFxzam1AB9JvsYvXryypfP 

[Bibiano_Geraldo · Accepted solution]

Hi i've removed some measures that i think that are unnecessary, my table is look like this:

Please download the file: Test file.pbix

[QQB]

Sorry after a close look,  found some issues:

1) When I removed the slicer filters, the Target_4 total became 0.

2) When the # of PMs performed_2 is empty, the values of PM_Monthly_Target_4 are not correct, the correct value should be 4 and 0.08 respectively as shown in the photo 2. 

Can you please kindly check and help again. Thank you! 

Can you please kindly check and help again? Thank you!

[Bibiano_Geraldo]

Hi @QQB ,

I will let you know soon about this, this occur because of SELECTEDVALUE function, because when you select more than one value in the slicer, the SELECTEDVALUE return blanks, because it expect a single value as output.

Thank you for let me know, i'll correct when im on PC

[QQB]

Amazing! This is exactly what I was hoping for,  your expertise is great appreciated, thank you!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the expected result there.

[QQB]

Sorry the data source is on a server and I am not able to share due to confidentiality reasons. 

[danextian]

You can remove the confidential data. What we need is a sample data that represents the actual one.

[QQB]

Unfortunately I have very limited access to the datasets... Potentially I could create it from sracth if needed. Thank you!