Forum Discussion

chrismcdonald's avatar
chrismcdonald
Icon for Helper I rankHelper I
6 years ago
Solved

Combine Measures

Hello,

 

I am trying to combine 2 measures that both work individually but I can't get them to work when combined. Please help.

 

The end result should only count the projects that matches the both criteria but also has all the subprojects completed for that project ID.

Example

Project IDSub ProjectDriverInstall Actual
11Urgent Work1/01/2020
12Urgent Work 
12Urgent Work1/01/2020
21Urgent Work2/02/2020
22Urgent Work2/02/2020
22Urgent Work

2/02/2020

 

Expected Result (showing only 1 because only 1 project has been fully completed)

 

DriverCount
Urgent Work1

 

This measure is used to roll up details to a project level. e.g 1 project ID might have multiple sub projects

 

Install Actual (Project Level) =

IF (

    CALCULATE (

        COUNTBLANK ( Table[Install Actual] ),

        ALLEXCEPT (Table, Table[Project ID] )

    ) > 0,

    BLANK (),

    CALCULATE ( MAX ( Table[Install Actual] ), ALLEXCEPT ( Table, Table[Project ID] ) ))

 

This measure is use to do a count of the Project ID's based on the filters

 

Urgent work Completed Last week=

         CALCULATE (

            DISTINCTCOUNT ( Table[Project ID] ),

            FILTER ( Table, Table[Week Index] = "-1" ),

            FILTER ('Table', Table[Driver] = "urgent work"))

 



Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    chrismcdonald , With description, I am not clear on missing measure.

    The table you want to show, all sub project completed


    countx(filter(summarize(Table,table[Project ID],"_1",count(Table[Sub Project]), "_2",countx(filter(Table,not(isblank(table[Install Actual]))),Table[Install Actual])),[_1] =[_2]),table[Project ID])

     

    If you want to work with the week. Please Use date dimension and Week rank like this

    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Refer my file

    https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    chrismcdonald , With description, I am not clear on missing measure.

    The table you want to show, all sub project completed


    countx(filter(summarize(Table,table[Project ID],"_1",count(Table[Sub Project]), "_2",countx(filter(Table,not(isblank(table[Install Actual]))),Table[Install Actual])),[_1] =[_2]),table[Project ID])

     

    If you want to work with the week. Please Use date dimension and Week rank like this

    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Refer my file

    https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0