Need help in DAX formula

[prasadavasare]

Need to add a column in above pivot which shows [Dezired result] the count of [Credit Family Names] whose [Sum of Sum of Primary used USD] is equal to Zero or Blank: Note: I am using Excel 2013 power pivot. No Power Query.

[TABLE="width: 374"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]<st1:stockticker>SCG</st1:stockticker>[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]PCD[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]<st1:stockticker>CPC</st1:stockticker>[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]CLT[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Bucket[/TD]
[TD](Multiple Items)[/TD]
[/TR]
[TR]
[TD]<st1:stockticker>COB</st1:stockticker>[/TD]
[TD]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 677"]
<tbody>[TR]
[TD]PCE[/TD]
[TD]Credit Family Name[/TD]
[TD]Sum of Sum of Primary Used USD[/TD]
[TD]Count of Credit Family Name[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]Fostxxxxxxxxx[/TD]
[TD]Rudixxxxxxxxxxx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Fostxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Gonzxxxxxxxxx[/TD]
[TD]Autixxxxxxxxxxxxxxx[/TD]
[TD="align: right"]6,922,542[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TURNxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]36,845,505[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gonzxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]43,768,047[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Indrxxxxxxxxx[/TD]
[TD]WATCxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]2,000,000[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Indrxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]2,000,000[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keglxxxxxxxxx[/TD]
[TD]PROMxxxxxxxxxxxxxxxx[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ROMOxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]3,577,278[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DEL xxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]3,646,318[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LUCExxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]5,938,030[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]INVExxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]25,000,000[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PABExxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]38,294,371[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keglxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]76,455,997[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Martxxxxxxxxx[/TD]
[TD]VINSxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PORTxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]STANxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]STANxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]3,090,534[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ALLExxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]11,082,173[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HORIxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]68,500,000[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Martxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]82,922,707[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]McNaxxxxxxxxx[/TD]
[TD]ABKCxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]McNaxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mendxxxxxxxxx[/TD]
[TD]BRC xxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]102,075,000[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mendxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]102,075,000[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portxxxxxxxxx[/TD]
[TD]RICHxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PALLxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]21,537,779[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]21,537,779[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Powoxxxxxxxxx[/TD]
[TD]AMERxxxxxxxxxxxxxxxx[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CENTxxxxxxxxxxxxxxxx[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]STANxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Powoxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Siegxxxxxxxxx[/TD]
[TD]STANxxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]352,236[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Siegxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]352,236[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Swicxxxxxxxxx[/TD]
[TD]ALL xxxxxxxxxxxxxxxx[/TD]
[TD="align: right"]1,083,463[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Swicxxxxxxxxx Total[/TD]
[TD][/TD]
[TD="align: right"]1,083,463[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]330,195,229[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[prasadavasare]

Need help urgently for dashboard

 

[prasadavasare]

Need help urgently for dashboard

I tried using below in power pivot dax function but unsuccessful:

Support1:=IF(SUM(Annual_Data_History[Sum of Primary Used USD])=0,CALCULATE(DISTINCTCOUNT(Annual_Data_History[PCE])),BLANK())

 

[akice]

you can try this:

measure := SUMX (
    VALUES ( Table[Credit Family Name] ),
    IF ( [Sum of Sum of Primary Used USD], 0, 1 )
)

But a bit of a shot in the dark.

 

[prasadavasare]

That works. Thank you so much.

One more help needed. Need to create Rag Rating Column at:
1 Credit Family Name level
2 PCE Level

1. Credit Family Name Level
if Max DPD >90 then R
if Max DPD >30 then A else G

2. PCE Level
if Max of Max DPD >90 then R
if Max of Max DPD >30 the A else G