Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
2 years ago
Solved

Help on Measure

How can I calculate online / offline % from my data by using a measure?

 

Table 1

Name

Unique

Status

Apple

1

Offline

Apple

2

Online

Apple

3

Online

Grapes

4

Online

Grapes

5

Offline

Grapes

6

Offline

Carrot

7

Online

Carrot

8

Online

 

 

Table 2 (Result)

Name

Offline

Online

Grand Total

Apple

33%

67%

100%

Grapes

67%

33%

100%

Carrot

0%

100%

100%

Grand Total

38%

63%

100%

 

 

Need Help
Show and Tell
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    Hi InsightSeeker 

     

    Download PBIX file with these examples

     

    Use these measures

     

     

    Online = 

VAR _Rows = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

VAR _OnlineRows = CALCULATE(COUNTROWS('DataTable'), FILTER((ALL('DataTable')), 'DataTable'[Status] = "Online" && 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

RETURN

DIVIDE(_OnlineRows, _Rows) + 0

     

     

     

    Offline = 

VAR _Rows = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

VAR _OfflineRows = CALCULATE(COUNTROWS('DataTable'), FILTER((ALL('DataTable')), 'DataTable'[Status] = "Offline" && 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

RETURN

DIVIDE(_OfflineRows, _Rows) + 0

     

     

     

    Regards

     

    Phil

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi InsightSeeker 

     

    Download PBIX file with these examples

     

    Use these measures

     

     

    Online = 

VAR _Rows = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

VAR _OnlineRows = CALCULATE(COUNTROWS('DataTable'), FILTER((ALL('DataTable')), 'DataTable'[Status] = "Online" && 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

RETURN

DIVIDE(_OnlineRows, _Rows) + 0

     

     

     

    Offline = 

VAR _Rows = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

VAR _OfflineRows = CALCULATE(COUNTROWS('DataTable'), FILTER((ALL('DataTable')), 'DataTable'[Status] = "Offline" && 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name])))

RETURN

DIVIDE(_OfflineRows, _Rows) + 0

     

     

     

    Regards

     

    Phil