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
Please help me to calculate in DAX this table.
I have the intial table named "Balance" which contains all client invoices.
I must display in another table the invoices for top five clients after column "Balance", without considering account "Accoun3".
So first i must do SUM on column "Balance" than order DESC, see which are top 5, show invoices for these clients and filer <> Acount 3.
The new tabel must contain only columns "Company code, Client name, Invoice ID, Balance and local account
Thank you.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file if it suits your requirement.
Expected result table =
VAR _t =
ADDCOLUMNS (
SUMMARIZE ( Balance, Balance[Company code], Balance[Client name] ),
"@balance total", CALCULATE ( SUM ( Balance[Balance] ) )
)
VAR _topfive =
WINDOW (
1,
ABS,
5,
ABS,
_t,
ORDERBY ( [@balance total], DESC ),
PARTITIONBY ( Balance[Company code] )
)
VAR _topfivelist =
SUMMARIZE ( _topfive, Balance[Company code], Balance[Client name] )
VAR _expectedresult =
CALCULATETABLE (
FILTER ( Balance, Balance[Local account] <> "Account 3" ),
TREATAS ( _topfivelist, Balance[Company code], Balance[Client name] )
)
RETURN
SUMMARIZE (
_expectedresult,
Balance[Company code],
Balance[Client name],
Balance[Invoice ID],
Balance[Balance],
Balance[Local account]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
HI,
Please try using Onedrive, Dropbox, or googledrive, and then share the link of the file.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file if it suits your requirement.
Expected result table =
VAR _t =
ADDCOLUMNS (
SUMMARIZE ( Balance, Balance[Company code], Balance[Client name] ),
"@balance total", CALCULATE ( SUM ( Balance[Balance] ) )
)
VAR _topfive =
WINDOW (
1,
ABS,
5,
ABS,
_t,
ORDERBY ( [@balance total], DESC ),
PARTITIONBY ( Balance[Company code] )
)
VAR _topfivelist =
SUMMARIZE ( _topfive, Balance[Company code], Balance[Client name] )
VAR _expectedresult =
CALCULATETABLE (
FILTER ( Balance, Balance[Local account] <> "Account 3" ),
TREATAS ( _topfivelist, Balance[Company code], Balance[Client name] )
)
RETURN
SUMMARIZE (
_expectedresult,
Balance[Company code],
Balance[Client name],
Balance[Invoice ID],
Balance[Balance],
Balance[Local account]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi and thank you for answer
There is an error at line 15 and i don't understand why
Hi, please share your sample pbix file.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi
Because i am a new user i cannot attach any file, pbix or XLSX.Can i use email or other method?
Thank you
HI,
Please try using Onedrive, Dropbox, or googledrive, and then share the link of the file.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
51 | |
47 | |
16 | |
13 |