?

[xyz1]

.

[xyz1]

.

[jonathandienst]

Data:
LOAD *,
  If(Classification <= 0.25 , '0-25%' ,
  If(Classification <= 0.50,  '26-50%',
  If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class
;
LOAD
  "NAME OF PROJECT (Code)",
  Phase,
  "Opportunity Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate(Data)
LOAD *,
  If(Classification <= 0.25 , '0-25%' ,
  If(Classification <= 0.50,  '26-50%',
  If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class
; 
LOAD
  "NAME OF PROJECT (Code)",
  Phase,
  "Feasibility Phase Completion %" as Classification
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate(Data)
LOAD *,
  If(Classification <= 0.25 , '0-25%' ,
  If(Classification <= 0.50,  '26-50%',
  If(Classification <= 0.75,  '51-75%',  '76-100%'))) as Class
; 
LOAD
  "NAME OF PROJECT (Code)",
  Phase,
  "Project Phase Completion %" as Classification 
FROM [lib://Test/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);

[Anonymous]

Hello Agrim,

I have made few changes to the expression in the chart. please find the below attachment. I have got the result as below. See if it can help you.

[Anonymous · Accepted solution]

Hello Agrim,

I have made few changes to the expression in the chart. please find the below attachment. I have got the result as below. See if it can help you. QVW is attached below.

[xyz1]

.

[Anonymous]

Hi Agrim,

In the backend, I have

LOAD [NAME OF PROJECT (Code)],
Phase,
num( [Opportunity Phase Completion %],'#,##0%') as  [Opportunity Phase Completion %],
num( [Feasibility Phase Completion %], '#,##0%')  as [Feasibility Phase Completion %],
num( [Project Phase Completion %], '#,##0%') as [Project Phase Completion %]
FROM

(ooxml, embedded labels, table is Sheet2);

In the Frontend:

Dimension: Stage

Expression 1:  (0-25)

=if(Phase='Project', count(if(([Project Phase Completion %]>='0%' and [Project Phase Completion %]<='25%') ,[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='0%'  and [Opportunity Phase Completion %]<='25%' ) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='0%' and [Feasibility Phase Completion %]<='25%') ,[NAME OF PROJECT (Code)])),
)))

Expression 2: (26-50)

=if(Phase='Project', count(if(([Project Phase Completion %]>='26%' and [Project Phase Completion %]<='50%') ,[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='26%'  and [Opportunity Phase Completion %]<='50%' ) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='26%' and [Feasibility Phase Completion %]<='50%') ,[NAME OF PROJECT (Code)])),
)))

Expression 3: (51-75)

=if(Phase='Project', count(if(([Project Phase Completion %]>='51%' and [Project Phase Completion %]<='75%') ,[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='51%'  and [Opportunity Phase Completion %]<='75%' ) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='51%' and [Feasibility Phase Completion %]<='75%') ,[NAME OF PROJECT (Code)])),
)))

Expression 4: (75-100)

=if(Phase='Project', count(if(([Project Phase Completion %]>='76%' and [Project Phase Completion %]<='100%') or isnull([Project Phase Completion %]),[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(([Opportunity Phase Completion %]>='76%'  and [Opportunity Phase Completion %]<='100%' ) or  isnull([Opportunity Phase Completion %]),[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(([Feasibility Phase Completion %]>='76%' and [Feasibility Phase Completion %]<='100%') or  isnull([Feasibility Phase Completion %]),[NAME OF PROJECT (Code)])),
)))

Expression 5: Null

=if(Phase='Project', count(if(isnull([Project Phase Completion %]),[NAME OF PROJECT (Code)])),
if(Phase='Opportunity',count(if(isnull([Opportunity Phase Completion %]) ,[NAME OF PROJECT (Code)])),
if(Phase='Feasibility',count(if(isnull([Feasibility Phase Completion %]) ,[NAME OF PROJECT (Code)])),
)))