Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure Optimization - IF() Reduction

Hello - I am trying to see if I can reduce the number of steps in the following measure.  Sub Measures are as follows:   [EstimatedTotalCost] is the sum of 4 individual columns that sit in the same...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    So you go with one measure, the same test

    test = 
VAR T1 =
    GENERATE (
        GROUPBY ( SalesHistory, SalesHistory[SO] ),
        VAR EstimatedLaborCost =
            CALCULATE ( SUM ( SalesHistory[Estimated LaborCost] ) )
        VAR EstimatedSubCost =
            CALCULATE ( SUM ( SalesHistory[Estimated SubcontractorCost] ) )
        VAR EstimatedMaterialCost =
            CALCULATE ( SUM ( SalesHistory[Estimated MaterialCost] ) )
        VAR EstimatedOtherCost =
            CALCULATE ( SUM ( SalesHistory[Estimated OtherCost] ) )
        VAR EstimatedTotalCost = EstimatedLaborCost + EstimatedSubCost + EstimatedMaterialCost + EstimatedOtherCost
        VAR EstimateQuality1 =
            SWITCH (
                TRUE (),
                ISBLANK ( EstimatedTotalCost ), BLANK (),
                ( EstimatedTotalCost > 0
                    && EstimatedMaterialCost = EstimatedTotalCost )
                    || EstimatedTotalCost = 0, "Incomplete",
                "Complete"
            )
        RETURN
            ROW ( "EstimateQuality1", EstimateQuality1 )
    )

VAR CompleteCount =    COUNTROWS ( FILTER ( T1, [EstimateQuality1] = "Complete" ) )
VAR TotalSO = COUNTROWS(VALUES(SalesHistory[SO]))
RETURN
DIVIDE(CompleteCount,TotalSO)

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

So you still have problem with this measure? Did not get what you want?

Can you provide some sample data with expected result? It is difficult to understand just based on a measure...

Var EstimateQuality =
SWITCH(
    TRUE(),
        ISBLANK(EstimatedTotalCost),BLANK(),
        (EstimatedTotalCost > 0
            && EstimatedMaterialCost = EstimatedTotalCost)
            || EstimatedTotalCost = 0 , "Incomplete",
            "Complete"
)

Var CountAllEstimates = 
COUNTROWS(VALUES(SalesHistory[SO]))

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous Of course.  I've uploaded it to GDocs.

 

Sample Data: https://drive.google.com/file/d/1Uu53QZFkrziBEdoLzORoY__dWnFJlnsj/view?usp=sharing

 

Sample PBI File: https://drive.google.com/file/d/1yxISUan6KIdk6nVKMUuKQTSGSLo80IYF/view?usp=sharing

 

The table is returning what I expect in the EstimateQualityCheck measure (complete/incomplete).  However, the aggregation isn't correct in the CompleteCount measure.

 

I'd expect that the 8 "Incompletes" would be excluded from the CompleteCount measure.  

 

The final calculation should return something in the ballpark of (147-8)/147 = 94.5%

 

In other words, why aren't these incomplete lines removing themselves from the calculation?

 

I also can't figure out why the CompleteCount is 148, not 147.  The Distinctcount([SalesHistory]SO) is working in the TotalCount measure.  But not when it's nested in the CompleteCount measure.

 

 

Thanks in advance for your continued support.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    I am running into another direction.

     

    If you are not calling a measure, but write them, wrap CALCULATE otherwise you sum up the whole table

    test =
VAR T1 =
    GENERATE (
        GROUPBY ( SalesHistory, SalesHistory[SO] ),
        VAR EstimatedLaborCost =
            CALCULATE ( SUM ( SalesHistory[Estimated LaborCost] ) )
        VAR EstimatedSubCost =
            CALCULATE ( SUM ( SalesHistory[Estimated SubcontractorCost] ) )
        VAR EstimatedMaterialCost =
            CALCULATE ( SUM ( SalesHistory[Estimated MaterialCost] ) )
        VAR EstimatedOtherCost =
            CALCULATE ( SUM ( SalesHistory[Estimated OtherCost] ) )
        VAR EstimatedTotalCost = EstimatedLaborCost + EstimatedSubCost + EstimatedMaterialCost + EstimatedOtherCost
        VAR EstimateQuality1 =
            SWITCH (
                TRUE (),
                ISBLANK ( EstimatedTotalCost ), BLANK (),
                ( EstimatedTotalCost > 0
                    && EstimatedMaterialCost = EstimatedTotalCost )
                    || EstimatedTotalCost = 0, "Incomplete",
                "Complete"
            )
        RETURN
            ROW ( "EstimateQuality1", EstimateQuality1 )
    )
RETURN
    COUNTROWS ( FILTER ( T1, [EstimateQuality1] = "Complete" ) )

     

     If you do have those measures, you can simply call it

    test1 =
VAR T1 =
    ADDCOLUMNS ( VALUES ( SalesHistory[SO] ), "test111", [EstimateQualityCheck] )
RETURN
    COUNTROWS ( FILTER ( T1, [test111] = "Complete" ) )

    I am not sure how your measure was working - COUNTAX part, but it basically counts the total row of your dummy data table -  there is one SO with 2 entries

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      That sure does work!  Thanks so much.

       

      One last thing.  How would you incorporate a final output of:

      Divide([Test],[TotalCount]... But do it in the same measure.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

         

        So you go with one measure, the same test

        test = 
VAR T1 =
    GENERATE (
        GROUPBY ( SalesHistory, SalesHistory[SO] ),
        VAR EstimatedLaborCost =
            CALCULATE ( SUM ( SalesHistory[Estimated LaborCost] ) )
        VAR EstimatedSubCost =
            CALCULATE ( SUM ( SalesHistory[Estimated SubcontractorCost] ) )
        VAR EstimatedMaterialCost =
            CALCULATE ( SUM ( SalesHistory[Estimated MaterialCost] ) )
        VAR EstimatedOtherCost =
            CALCULATE ( SUM ( SalesHistory[Estimated OtherCost] ) )
        VAR EstimatedTotalCost = EstimatedLaborCost + EstimatedSubCost + EstimatedMaterialCost + EstimatedOtherCost
        VAR EstimateQuality1 =
            SWITCH (
                TRUE (),
                ISBLANK ( EstimatedTotalCost ), BLANK (),
                ( EstimatedTotalCost > 0
                    && EstimatedMaterialCost = EstimatedTotalCost )
                    || EstimatedTotalCost = 0, "Incomplete",
                "Complete"
            )
        RETURN
            ROW ( "EstimateQuality1", EstimateQuality1 )
    )

VAR CompleteCount =    COUNTROWS ( FILTER ( T1, [EstimateQuality1] = "Complete" ) )
VAR TotalSO = COUNTROWS(VALUES(SalesHistory[SO]))
RETURN
DIVIDE(CompleteCount,TotalSO)