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.
Good morning,
i have a list with values and a specific "position", which is a clear value at the table:
Here is the table
How can i get the list filled with the sum of the positions as shown in the example above. It would be great to get your help.
Solved! Go to Solution.
Hi,
Total calculation logic uses the same dax as within the columns/rows it is trying to calculate total. However in the total calculation filter context e.g. using SELECTEDVALUE() return blanks. To combat this you can change the calculation logic of the measure for total values e.g. IF(SELECTEDVALUE('Table'[month])=blank(),[total calculation logic],[original calculation logic])
Proud to be a Super User!
Great! Thanks for your help!
@ValtteriN Thanks a lot - this helps! I think you switched the table names in one post, as i get the values from the list and not the table. Anyway, i found the problem. Another question. Is there a reason, why i don´t get the total amount in columns and rows?
Hi,
Total calculation logic uses the same dax as within the columns/rows it is trying to calculate total. However in the total calculation filter context e.g. using SELECTEDVALUE() return blanks. To combat this you can change the calculation logic of the measure for total values e.g. IF(SELECTEDVALUE('Table'[month])=blank(),[total calculation logic],[original calculation logic])
Proud to be a Super User!
@ValtteriN Thanks for the explanation. Where do i have to add the measure or function? Is the function correct? I don´t see the part where you grab the value from the INCOMING (list)?
@SvenJ
I understood you want the values from the table with month names as columns. Did I misunderstand. In the DAX I categorize whether or not values are incoming or out going based on if they are positive or negative and then I created two measures based on this. E.g. if the value is -5 for position 4444 in January the result is outgoing = -5 and incoming = blank() for the month and position in question.
Proud to be a Super User!
@ValtteriN No, the other way around. I want to read the values from the list and want the sum in the table with month
This clarifies things,
The unpivot step remains the same, but instead of 2 measures we can use one.
Example data with incoming + outgoing columns:
Dax:
Proud to be a Super User!
Hi,
You could Unpivot the table and then create either a columns or a measures for incoming and outgoing amounts. Additionally link the two tables with dimension tables. The condition for incoming and outgoing can be their value direction.
E.g. Incoming = IF(sum('Incomingfact'[Value])>=0,sum('Incomingfact'[Value]),BLANK())
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Thanks for your answer. Can you give me an example? I´m new on Power Bi and can´t follow you 100%
Okay,
So for the steps:
Unpivot:
Select a column in powerquery and "unpivot other columns" or in this case select month column January, February....
You get a table like this:
Dimension tables:
So with this I mean tables that act as a slicer in between your two tables e.g. Calendar with Month column or a list of all the positions. Alternatively you can ignore this step and modify the measures for incoming and outgoing.
As an example here 'IncomingFact' table is a table that I unpivoted and 'Incoming' Table is the "list" table:
'IncomingFact'
End result:
For outgoing I reversed the "<" condition
This should be a more detailed explanation. Ping me with @ if you still have questions.
Proud to be a Super User!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |