Forum Discussion

PBI5851's avatar
PBI5851
Helper V
2 years ago
Solved

Measure using a measure or create a column

Hello team,  My raw data is as below.  Region State TM ActiveCount InactiveCount West CA Tim 5 10 West AZ Mark 8 5 West CA Steve 6 5 West CA Mark 5 5 West CO ...
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    PBI5851 try these measures:

     

    Sum Active = SUM ( TableActive[ActiveCount] )

Active +1 = 
VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Sum Active] )
RETURN
    SUMX ( 
        __Table,
        IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] )
    )

Active +2 = 
VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +1] )
RETURN
    SUMX ( 
        __Table,
        IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] )
    )

Active +3 = 
VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +2] )
RETURN
    SUMX ( 
        __Table,
        IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] )
    )

Total = [Sum Active] + [Active +1] + [Active +2] + [Active +3]
parry2k's avatar
parry2k
Super User
2 years ago

PBI5851 try these measures:

 

Sum Active = SUM ( TableActive[ActiveCount] )

Active +1 = 
VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Sum Active] )
RETURN
    SUMX ( 
        __Table,
        IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] )
    )

Active +2 = 
VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +1] )
RETURN
    SUMX ( 
        __Table,
        IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] )
    )

Active +3 = 
VAR __Table = ADDCOLUMNS ( SUMMARIZE ( TableActive, TableActive[TM] ), "@ActiveCount", [Active +2] )
RETURN
    SUMX ( 
        __Table,
        IF ( [@ActiveCount] < 15, [@ActiveCount] * 1.15, [@ActiveCount] )
    )

Total = [Sum Active] + [Active +1] + [Active +2] + [Active +3]