cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ito_c
Frequent Visitor

Force "0" where I don't have variable return

Hi Everyone, 

 

I have a small dataset (in the below table). 

Fiscal_YearFiscal_MonthSubject_TitleEvaluation
FYear20FMonth11Algebra IGood
FYear20FMonth11Algebra IIBad
FYear20FMonth11Algebra IIIBad
FYear20FMonth12StatisticNeutral
FYear21FMonth01Social ScienceBad
FYear21FMonth01AlgorithmsGood

 

With DAX formula (creadits to Tahreem24) was possible return the percentage for each "Evaluation" by "Fiscal_Month". 

 

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_)

 

Fiscal_YearFiscal_MonthSubject_TitleEvaluation%
FYear20FMonth11Algebra IGood33%
FYear20FMonth11Algebra IIBad33%
FYear20FMonth11Algebra IIIBad33%
FYear20FMonth12StatisticNeutral100%
FYear21FMonth01Social ScienceBad50%
FYear21FMonth01AlgorithmsGood50%

 

So, now I need to return in a Card only the Neutral percenage for last Fiscal_Month. I don't have for the last Fiscal_Month any value for "Neutral" Evaluation, because of this the KPI card is returnig the value 100% for previus Fiscal_Month (FMonth12). I tryed to use as base some formulas to fill with "0" in case of sales when sales value is empty, but not work as expected. 

 

I need just filter "Evaluation" as "Neutral" and return the last "Fiscal_Month" value, but in this case does anybody knows how to return "0" value when I don't have "Neutral" Evaluation for the last Fiscal_Mont "FMonth01"?

1 ACCEPTED SOLUTION

Hi, @Ito_c 

If you want to fill with "0" in case of sales when sales value is empty, you need to add a separate dimension table for Evaluation, and then try measure mentioned by amitchandak.

20.png

21.png

22.png

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Ito_c , have tried

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_,0)

 

or

 

% Eva =
VAR a_ = CALCULATE(COUNT(Table1[Fiscal_Month]))
VAR b_ = CALCULATE(COUNT(Table1[Fiscal_Month]),ALLEXCEPT('Table1',Table1[Fiscal_Month]))
RETURN DIVIDE(a_,b_,0)+0



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi @amitchandak, include just "0" doesn't work in this case, keeping returning the previous Fiscal_Month where there is a value for column Evaluation:

 

ForumDoubt4.JPG

 

I looking for this: 

ForumDoubt5.JPG

Hi, @Ito_c 

If you want to fill with "0" in case of sales when sales value is empty, you need to add a separate dimension table for Evaluation, and then try measure mentioned by amitchandak.

20.png

21.png

22.png

Best Regards,
Community Support Team _ Eason

Thank you @v-easonf-msft works fine. Thank you @amitchandak for your support too!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors