Dax is not showing KPI by month when value is 0

[manoplaeli]

Hi, below is my data table. 

I need to build KPI summary by month. The paramitor for kpi is "Spending amount of on time/Total spending in that month" below is my Dax formular. The issue here is there is no "On time" status in August and that the mattrix doen't show any value while I would like to display 0% in August.

KPI = (SUMx(FILTER(Table1,Table1[On time?]="On time"),Table1[Total Spend])/SUMX(Table1,Table1[Total Spend]))

location	Date	Total Spend	On time?
location A	10-Mar-17	40000	On time
location B	17-Mar-17	292345	On time
location A	6-Apr-17	237548	On time
location A	10-May-17	55707	Not on time
location A	6-Jul-17	687226	Not on time
location B	28-Jul-17	1243027	On time
location B	28-Jul-17	1243047	Not on time
location A	31-Jul-17	69819	On time
location A	31-Jul-17	71619	On time
location A	31-Jul-17	60984	On time
location A	6-Aug-17	23000	Not on time
location B	18-Aug-17	28015	Not on time
location A	29-Aug-17	15156	Not on time

Thanks,

[joell001213]

명동핸플[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰 #아밤ψ
>조엘0013<

명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동핸플[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동핸플[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

명동핸플[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

[joell001213]

명동안마[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰 #아밤ψ
>조엘0013<

명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동안마[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동안마[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

명동안마[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

[joell001213]

명동오피[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰 #아밤ψ
>조엘0013<

명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
명동오피[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳명동오피[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

명동오피[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

[v-sihou-msft]

@manoplaeli

In this scenario, since both locations has blank values on Aug, Power BI will not display the entries in visual by default. You can select "Show items with no data" on both Row and Column fields.

Or you can modifly your measure to display 0 when the result is blank.

KPI =
IF (
    ISBLANK (
        SUMX ( FILTER ( Table1, Table1[On time?] = "On time" ), Table1[Total Spend] )
            / SUMX ( Table1, Table1[Total Spend] )
    ),
    0,
    SUMX ( FILTER ( Table1, Table1[On time?] = "On time" ), Table1[Total Spend] )
        / SUMX ( Table1, Table1[Total Spend] )
)

Regards,

[manoplaeli]

@v-sihou-msft

Thank you very much for your suggestion. My database has Mar,Apr,May, Jul, and August which in August I have 0% KPI and that because there is no ontime value. Is it possible to distinquist between 0% KPI and not applicable data? In my case I don't want to display Sep - Dec because data is not yet available. In addition, April>>location B should be blank instead of 0%. Not applicable data and 0% value has different meaning in this case.

Below is PBX file

Download

Thank you inadvance

[Ashish_Mathur]

Hi,

I have been able to solve your problem partly.  See screenshot below.  Download the workbook from here.

[manoplaeli]

@Ashish_Mathur@v-sihou-msft

Hi Both,

Any other suggestion to make this KPI show completly correct?

Many thanks,

Manop

[manoplaeli]

@Ashish_Mathur

Thanks, let me study and see if can make it fully complete. any other idea to make it fully solved is welcome.

Best,