Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RenanMoreira
Frequent Visitor

Display values from a colunm that has zero values

Hello!

 

I Have and budget report, wich i use 2 tables, one from SAP ECC, containing all of our costs, and other one is a budget one

 

To match both of them, i have a colunm to concatenate unique values, then use they as a key to create a DAX, similiar to SUMIFS from Excel:

 

 

ALCULATE(SUM('Database'[Values]), 
        FILTER(ALL('Database'[Auxiliar]), 'Database'[Auxiliar] = 'BudgetBase'[Auxiliar])

 

 

And that gives me the full value of my expends in a cost center and account, for example.

 

But the problem is, when i create a table with Real vs Budget, IF my Cost Center hasn´t had any release this month, then, my budget will not appear too, on the same table, wich leads to a non-real budget value:

 

RenanMoreira_0-1610627684605.png

 

Translation: Filters are = Month = December, All Costs Centers, All Areas, and Account is "Coffe Supplies"

 

So, the table tells me that i have a 2.796,66 Budget, and already spent 10k, but, when i check my database using the same filters, you can see that i have more:

 

 
 

123456.png

 

P.S: "Orçamento" stands for Budget in Brazilian Portuguese

 

So, i know that the error was,

the employee made the register on the same cost center, so he spend all the plant budget on the same cost center, but how do i get to be able to see the budget on all costs centers, not only the ones that had costs registered?

 

P.S: Both of the Auxiliar colunms in both table are relationed
Auxiliar stands for a concatenate between Accout, Cost Center, month and yeAR, USING & NOT CONCATENATE DAX Formula.

 

Any ideas?

 

Regards,

 

 

 

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @RenanMoreira 

 

Sorry but your setup is not fully understandable is it possible to share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Believe that the error may be related with the relationship between your montlhy budget and the monthlhy expenses also.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @RenanMoreira 

 

Sorry but your setup is not fully understandable is it possible to share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Believe that the error may be related with the relationship between your montlhy budget and the monthlhy expenses also.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.