Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello good morning, I have a question.
I have this matrix with Idtipoliq in rows and its final turnover and that of the previous year.
To calculate ADJUST I did: sum('Invoices Annex'[ADJUST AMOUNT])
The case is that I need that:
- For 2018;
if idtipoliq="CP" then I need to add the value 952721 to the total invoicing.
if idtipoliq="TV" then I need the value 622131 to be added to the billing total.
-For 2020: add the value 752000 to the billing total.
I am trying to create a measure but I don't know how to set the year or idtipoliq filters.
THANKS IN ADVANCED
@afrutos Column references in measures need an aggregation like:
MAX('TableName'[idtipoliq])
So how do you suggest to do the calculation?
Because I think it should be a measure, otherwise it adds that value to each row of invoices, and I just want to add it to the total sum.
I'm trying this but it doesn't work either.
ADJUST Final Invoice =
SUMX(
'Attachment Invoices',
IF('Invoices Attachment'[INVOICE YEAR]=2018 && 'Invoices Attachment'[Idtipolyqaccounting 2]="CP", VALUE(SUM('Invoices Attachment'[ADJUST AMOUNT]))+952721.8,
IF( 'Invoices Attachment'[INVOICE YEAR]=2018 &&'Invoices Attachment'[Idtipolyqaccounting 2]="TV", SUM('Invoices Attachment'[ADJUSTMENT AMOUNT])+622131.45,
IF( 'Invoices Attachment'[INVOICE YEAR]=2020, SUM('Invoices Attachment'[ADJUSTMENT AMOUNT])+752152,
'Invoices Attachment'[ADJUSTMENT AMOUNT]))))
@afrutos If you just want to add it to the total, use HASONEVALUE('Table'[idtipoliq]), if true, return your value, otherwise return the value + whatever you want to add.
@Greg_Deckler
But how do I filter it in that case that i only want it to apply for Idtipoliq="CP" and Year=2018?
What would be nice is to be able to put those casuistics into a metric, as I then need to put it into the accumulated.
- For 2018;
if idtipoliq="CP" then I need to add the value 952721 to the total invoicing.
if idtipoliq="TV" then I need the value 622131 to be added to the billing total.
-For 2020: add the value 752000 to the billing total.
I am trying to create a measure but I don't know how to set the year or idtipoliq filters.
Could you explain yourself in an example please?
thanks
@afrutos OK, this will go far easier if you provide the following:
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
See if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |