In a matrix I can't get the data of a calculated field to be added to me

[Syndicate_Admin]

Hello

I want to know how many documents issued are less than an amount of €50.

To do this, I've created a matrix using the salesperson, document number, amount, and customer fields.

To give an example, I add the following table (made in excel):

Invoice No.	Matter	Commercial	Customer (name)
Invoice 1	45,00 €	Commercial 1	Client 1
Invoice 2	9,00 €	Commercial 2	Customer 2
Invoice 3	15,00 €	Commercial 3	Client 3
Invoice 4	4,00 €	Commercial 4	Client 4
Invoice 5	18,00 €	Commercial 1	Customer 5
Invoice 6	32,00 €	Commercial 2	Customer 6
Invoice 7	68,00 €	Commercial 3	Client 7
Invoice 8	89,00 €	Commercial 4	Customer 8
Invoice 9	34,00 €	Commercial 1	Client 1
Invoice 10	86,00 €	Commercial 2	Customer 2
Invoice 11	28,00 €	Commercial 3	Client 3
Invoice 12	69,00 €	Commercial 4	Client 4
Invoice 13	71,00 €	Commercial 1	Customer 5
Invoice 14	48,00 €	Commercial 2	Customer 6
Invoice 15	48,00 €	Commercial 3	Client 7
Invoice 16	79,00 €	Commercial 4	Customer 8
Invoice 17	8,00 €	Commercial 1	Client 1
Invoice 18	80,00 €	Commercial 2	Customer 2
Invoice 19	11,00 €	Commercial 3	Client 3
Invoice 20	18,00 €	Commercial 4	Client 4
Invoice 21	12,00 €	Commercial 1	Customer 5
Invoice 22	77,00 €	Commercial 2	Customer 6
Invoice 23	70,00 €	Commercial 3	Client 7
Invoice 24	70,00 €	Commercial 4	Customer 8
Invoice 25	52,00 €	Commercial 1	Client 1
Invoice 26	92,00 €	Commercial 2	Customer 2
Invoice 27	53,00 €	Commercial 3	Client 3
Invoice 28	78,00 €	Commercial 4	Client 4
Invoice 29	44,00 €	Commercial 1	Customer 5
Invoice 30	90,00 €	Commercial 2	Customer 6

I've come up with the idea of using the formula X= IF (AMOUNT<50.1) to create a "calculated field" that returns a 1 if the value of the amount field is less than €50 and a 0 if it's €50 or more. The formula works but the problem is that in the totalized the "1" generated in this new column is not added.

In the image below, there are 2 documents that do not reach 50€ and the 1 appears in the calculation column, but then these values are not added in the first row, which is the seller's aggregate, and should be added in the same way as sales do. (Power BI Actual Capture) In the left column we have the seller and below the documents with their amount and the calculated field if it is <50

The general idea would be to have a field with the total number of documents issued and another field with the number of documents that do not exceed the €50 barrier. In addition, with the customer filter you can also compare which customers place small orders.

Going back to the first table that I have given as an example, the result should be something like this:

I have made this table with Excel pivot tables but in power BI I can't make this idea work.

I obtain the data through a semantic model, I don't know if it has anything to do with applying the formulas.

Any recommendations?

Thank you

[Syndicate_Admin]

Hola @Syndicate_Admin,

I have been doing tests with the file you have sent me but, in the table I use, the amount of the invoice does not appear, the amount of each line of item appears within the invoice so I have to make a sum of all the amounts according to the invoice code.

I think what I'm going to do for the moment is move all the documents in the system to excel and create a new workspace that depends on that excel.

Thanks for the help,

[v-venuppu]

Hi @Syndicate_Admin ,

As you are facing difficulty in implementing the proposed solution, I have developed a PBIX file using the sample data given in original post and I was able to match the output.Please go through the attached PBIX file for your reference.

Thank you.

[v-venuppu]

Hi @Syndicate_Admin ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @GrowthNatives for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Syndicate_Admin]

Good morning

First thank you for the time spent on the problem.

I am having difficulty implementing the proposed solution.

The first is that I work on the web version and I haven't found a way to create the "Measures" there.

I have switched to the desktop version to be able to test and at the moment I have not been able to implement the new measures.

The reason is that I don't have a field that is "Invocies-Amount" as such, but the value of the document is given by the sum of the lines (products) contained in it. In such a way that invoice X is worth €10 because the sum of products A, B and C, which are inside that document, are worth €10.

Now I'm trying to create the field that adds the lines of the document to get that "Invocies-Amount" field. Is there a more suitable formula for this objective? It would be adding the value "Net Amount" based on the "Document Name"

For the sum of documents I have used the formula:

Countrow no Alb = CALCULATE(COUNTROWS(VALUES(VENTAS[Num. Documento])))

In this way, it only counts the documents issued and not the lines of each document.

Thank you again.

[GrowthNatives]

Hi @Syndicate_Admin ,
The best solution to cater this problem would be to create DAX Measures not calculated columns 

1. Create a measure to count all invoices 

DAX
Total Docs = COUNTROWS('Invoices')

2. Create a measure to count invoices with docs< 50

DAX 
Docs < 50 = 
CALCULATE(
    COUNTROWS('Invoices'),
    'Invoices'[Amount] < 50
)

3. Put these values in your matrix

• Rows: Commercial and Customer

• Values: Total Docs and Docs < 50

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]