cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
manoplaeli Regular Visitor
Regular Visitor

Dax is not showing KPI by month when value is 0

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]))

 

 

Capture.JPG

 

locationDateTotal SpendOn time?
location A10-Mar-1740000On time
location B17-Mar-17292345On time
location A6-Apr-17237548On time
location A10-May-1755707Not on time
location A6-Jul-17687226Not on time
location B28-Jul-171243027On time
location B28-Jul-171243047Not on time
location A31-Jul-1769819On time
location A31-Jul-1771619On time
location A31-Jul-1760984On time
location A6-Aug-1723000Not on time
location B18-Aug-1728015Not on time
location A29-Aug-1715156Not on time

 

Thanks,

8 REPLIES 8
Moderator v-sihou-msft
Moderator

Re: Dax is not showing KPI by month when value is 0

@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.

 

1233.PNG

 

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 Regular Visitor
Regular Visitor

Re: Dax is not showing KPI by month when value is 0

@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

 

Capture.JPG

 

Download

 

Thank you inadvance

Super User
Super User

Re: Dax is not showing KPI by month when value is 0

Hi,

 

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

 

Untitled.png

joell001213 Member
Member

Re: Dax is not showing KPI by month when value is 0명동오피[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰

명동오피[오피투데이(오투)☞☜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 Member
Member

Re: Dax is not showing KPI by month when value is 0명동안마[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰

명동안마[오피투데이(오투)☞☜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#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

manoplaeli Regular Visitor
Regular Visitor

Re: Dax is not showing KPI by month when value is 0

@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,

joell001213 Member
Member

Re: Dax is not showing KPI by month when value is 0명동핸플[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰

명동핸플[오피투데이(오투)☞☜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#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

manoplaeli Regular Visitor
Regular Visitor

Re: Dax is not showing KPI by month when value is 0

@Ashish_Mathur@v-sihou-msft

 

Hi Both,

 

Any other suggestion to make this KPI show completly correct?

 

Many thanks,

Manop

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 126 members 1,492 guests
Please welcome our newest community members: