Show a measure in and specific row of the matrix visualization

[MacarenaGB]

Hi!

I am creating a table that shows the expenses according to the date:
If the month selected is before the current one, it takes the real data and it is prorrated to the man-days expended in each project. (Correct behaviour)
Else, it calculates an avererage but I want to associate it to "Internal Project/non Billable" instead of prorrated to every project. I tried with a Switch function but it is not working

VAR Estimated =    
IF(
    DATE(SELECTEDVALUE(Dim_Calendar[CalendarYear]), SELECTEDVALUE(Dim_Calendar[CalendarMonth]),1) >=  DATE(YEAR(TODAY()), MONTH(TODAY()),1),
    SWITCH(
            TRUE(),
            VALUES('Fact_Effort Tracking'[Project Name]) = "Int - Internal Project/Non billable", [Estimated Periodical Expense - P&L],
            BLANK()
        )
)

VAR Real =
        IF(
            DATE(SELECTEDVALUE(Dim_Calendar[CalendarYear]), SELECTEDVALUE(Dim_Calendar[CalendarMonth]),1) <  DATE(YEAR(TODAY()), MONTH(TODAY()),1),
            CALCULATE(
                SUMX(Fact_Expenses,Fact_Expenses[Total w/o VAT €]),
                Fact_Expenses[Initial Investment] = "No",
                USERELATIONSHIP(Fact_Expenses[Invoicing Date], Dim_Calendar[DateKey])    
            ),
            BLANK()
        )


VAR TotalCost =
        Estimated
            
        +      
        
        DIVIDE(
            (Real)* DaysProject,
            TotalDays
         )

Return TotalCost

How can I  associate a measure to a single row?

TIA!

[v-deddai1-msft]

Hi @MacarenaGB ,

I have seen your solution in the other post above. Thank you for sharing the solution.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @MacarenaGB ,

You should use SUMX in your formula like: SUMX(Table,[Your measure]). For more details, please refer to https://blog.enterprisedna.co/fixing-incorrect-totals-using-dax-measures-in-power-bi/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[tex628]

If i understand you correctly you want the estimated value to only populate the non billable row? 

What are you getting if you try this?

VAR Estimated =    
IF(
    DATE(SELECTEDVALUE(Dim_Calendar[CalendarYear]), SELECTEDVALUE(Dim_Calendar[CalendarMonth]),1) >=  DATE(YEAR(TODAY()), MONTH(TODAY()),1),
    SWITCH(
           SELECTEDVALUE('Fact_Effort Tracking'[Project Name]),
           "Int - Internal Project/Non billable", [Estimated Periodical Expense - P&L],
           BLANK()
        )
)

[MacarenaGB]

Hello! Thanks for answering!

I tried it but it show everything blank. I am so confused

[tex628]

Alright,  

Let's start by determining what the issue is. 

If you start by breaking out the "estimated" part of the measure as we know the issue is with that segment. Create a new temporary measure that we can work with. 

There are a few things we should try, 

First we need to make sure that the measure [Estimated Periodical Expense - P&L] is returning a value on the Internal Project/Non billable line. Try returning only [Estimated Periodical Expense - P&L] and check to see that you're getting the values you're looking for on that row. 

Next we want to check the date filter. You can do this by removing everything but the initial date filter. Then have it return "1" instead and check to see that it's returning true on the correct dates. 

Finally we want to check the selectedvalue() statement in the estimated variable. remove the Measure from teh switch statement and return a "1" instead. This should allow you to make sure that the switch statement is working as intended. 

When you have checked all of these segments you should know what is wrong and through that also be able to fix the issue. 

Let me know how it goes!

Br,
J

[MacarenaGB]

Ok!! 

Here: 
Check 1) So, it is not working the condition of the "Int - Internal Project/Non billable". But I think that use SelectedValue maybe it is not right because I am not selecting it. It is "showed" in the matrix. That's all.

VAR Estimated

IF(
    DATE(SELECTEDVALUE(Dim_Calendar[CalendarYear]), SELECTEDVALUE(Dim_Calendar[CalendarMonth]),1) >=  DATE(YEAR(TODAY()), MONTH(TODAY()),1),
    SWITCH(
           SELECTEDVALUE('Fact_Effort Tracking'[Project Name]),
           "Int - Internal Project/Non billable",
            1 ,
           "BLANK"
        ),
    "Out of date"
        
)

Even if I select the name, it is still showing "BLANK"

What do you think with this info?
I really appreciate your help. so nice from u! @tex628 

[MacarenaGB]

Hi @tex628 

I got it!! 
I am using ISINSCOPE additionally 

VAR Estimated =  
IF(
    ISINSCOPE('Dim_Project Database'[Program Name]),
    SWITCH(
                SELECTEDVALUE('Dim_Project Database'[Program Name]),
                "Non billable" , [Estimated Periodical Expense - P&L] * NumberOfMonths
                ,
                 BLANK()
                )
)

Now, the only problem it that with ISINSCOPE, the grand total is not showed.
Do you know how to fix that?

[tex628]

Hi! 

I believe the reason for the first attempt not working is that we have both "Program Name" and "Project Name" and we are using the wrong one in the calculation:

[MacarenaGB]

Hi! I post another doubt and finally both are related:
https://community.powerbi.com/t5/Desktop/Total-is-empty/m-p/1658382#M663555 

(I'm trying to do to complex think for me... hahaha )

Program and Project Name are related anyway.

In overall, I am trying to fix the Resource column first and write estimated cost for the future and add the real cost of the past.
Then, I am trying to prorrate the Monthly Expense Prorrated according to that Resource column