Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Need help in row level context in DAX

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Need help in row level context in DAX

09-12-2017
02:06 PM

Hi Guys,

I have a below table :

Factor | ClassSubclass | Fee Received | Modality | Manager | Score |

0.0004 | 112 | 51000 | Uniform | Sunnyvale | 19 |

0.0005 | 122 | 60000 | Uniform | Sunnyvale | 17 |

0.0005 | 132 | 70000 | Uniform | Sunnyvale | 10 |

I have calculated the score like this: SUMX ( Factor, [Fee received] * [Factor] ) , but what i actually want is sum of Fee Received multiplied by the factor of that ClassSubclass, Modality and Manager. i.e. 51000+60000+70000 = 181000 * 0.0004 = 72.4.

How do I achieve this?

Regards,

Shweta

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2017
07:06 AM

Hi mattbrice,

i pasted this:

My Health Score =

VAR all_fees =

Calculate( Sum(Remittance[Amount paid]), All(Remittance))

VAR this_factor =

SELECTEDVALUE ( Factor[Factor] )

RETURN

this_factor * all_fees

and i got duplicates(couldnt attach a screenshot!), but it was definitely giving me wrong values , hence i did this:

My Health Score =

VAR all_fees =

Sum(Remittance[Amount paid])

VAR this_factor =

SELECTEDVALUE ( Factor[Factor] )

RETURN

this_factor * all_fees

and got below:

Factor | ClassSubclass | Fee Received | Modality | Manager | Score |

0.0004 | 112 | 51000 | Uniform | Sunnyvale | 19 |

0.0005 | 122 | 60000 | Uniform | Sunnyvale | 17 |

0.0005 | 132 | 70000 | Uniform | Sunnyvale | 10 |

0.0014 | 1810000 |

which is still not satisfying my need. i need 181000*0.0004 = 72.4 for subclass 112 and so on. thsi should be done dynamically on row level.

My current formula which is **SUMX ( Factor, [Fee received] * Factor[Factor] )** is just multiplying the respective row to corresponding row, i.e 51000*0.0004 and 60000*0.0005 and so on. but i want a sum of the fee received for that particular modality and manager and multiply it by the corresponding factor of the subclass.

Regards,

Shweta

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2017
09:07 AM

Hi All,

Thank you all for your help. Looks like my client was unclear as to what he wanted. As of now my formula is validated and looks correct , hence i am just closing this topic. Thanks again for all the help!

Regards,

Shweta

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
02:41 PM

as a calculated column:

measure = VAR all_fees = SUM ( Table[Fee Received] ) RETURN Table[Factor] * all_fees

as a measure, it depends on what other rows,columns, filters, slicers, etc are being applied, but something like this:

measure = VAR all_fees = CALCULATE ( SUM ( Table[Fee Received] ), ALL ( Table ) ) VAR factor = SELECTEDVALUE ( Table[Factor] ) RETURN factor * all_fees

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
02:52 PM

Hi Mattbrice,

Thanks for the formula, i tried and not working, it says:

'factor' is a table name and cannot be used to define a variable.

Please help! i am new to DAX and finding it difficult to achieve this particular formula!

Regards,

Shweta

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
02:54 PM

means you need to change the name of the variable i declared as it conflicts with a table name in your model.

measure = VAR all_fees = CALCULATE ( SUM ( Table[Fee Received] ), ALL ( Table ) ) VAR this_factor = SELECTEDVALUE ( Table[Factor] ) RETURN this_factor * all_fees

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
03:02 PM

oh ok i renamed the variable, but now it just gives me

19 |

17 |

10 |

still doesnt give me the exact number 😞 😞 😞 which is 72.4 for classSubclass 112 ,181000*0.0004 = 72.4

Regards,

Shweta

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
11:42 PM

Hi @shwets47,

Your get Fee received from different tables, for example Fee received1= SUM(Table1[Amount paid]),Fee received2= SUM(Table2[Amount paid]), or Fee received3= SUM(Table3[Amount paid]).

Please try the formula and check if it works fine.

result=(SUM(Table1[Amount paid])+SUM(Table2[Amount paid])+SUM(Table3[Amount paid]))*Factor[factor]

Do you mind share your .pbix file for further analysis? It's hard to reproduce your scenario, so you'd better share more details.

Best Regards,

Angelia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2017
06:50 AM

Hi Angelia,

Thank you for your reply. But your formula wouldnt work for me for the same reason as Tom's, it says

"A single value for column 'Factor' in table 'Factor' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

but i need a single value from the column Factor multiplying with, sum of the amount paid. I know there is something that is very simple and I am missing it!

Sorry i wouldnt be able to share the pbix file as its a confidential client data and i cannot share it with anyone.

Regards,

Shweta

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
03:05 PM

can you paste in here what the exact measure is your wrote?

At this point me (or someone else) would need to get a sample copy of your model to really help. Too many potential issues to comment back and forth.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2017
07:06 AM

Hi mattbrice,

i pasted this:

My Health Score =

VAR all_fees =

Calculate( Sum(Remittance[Amount paid]), All(Remittance))

VAR this_factor =

SELECTEDVALUE ( Factor[Factor] )

RETURN

this_factor * all_fees

and i got duplicates(couldnt attach a screenshot!), but it was definitely giving me wrong values , hence i did this:

My Health Score =

VAR all_fees =

Sum(Remittance[Amount paid])

VAR this_factor =

SELECTEDVALUE ( Factor[Factor] )

RETURN

this_factor * all_fees

and got below:

Factor | ClassSubclass | Fee Received | Modality | Manager | Score |

0.0004 | 112 | 51000 | Uniform | Sunnyvale | 19 |

0.0005 | 122 | 60000 | Uniform | Sunnyvale | 17 |

0.0005 | 132 | 70000 | Uniform | Sunnyvale | 10 |

0.0014 | 1810000 |

which is still not satisfying my need. i need 181000*0.0004 = 72.4 for subclass 112 and so on. thsi should be done dynamically on row level.

My current formula which is **SUMX ( Factor, [Fee received] * Factor[Factor] )** is just multiplying the respective row to corresponding row, i.e 51000*0.0004 and 60000*0.0005 and so on. but i want a sum of the fee received for that particular modality and manager and multiply it by the corresponding factor of the subclass.

Regards,

Shweta

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-14-2017
06:44 PM

Hi @shwets47,

It's hard to reproduce your scenario without sample table, I totally understand your data is confidential, you can create similar fake data. So that we can post solution which is close to your requirement.

Best Regards,

Angelia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2017
09:07 AM

Hi All,

Thank you all for your help. Looks like my client was unclear as to what he wanted. As of now my formula is validated and looks correct , hence i am just closing this topic. Thanks again for all the help!

Regards,

Shweta

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
02:40 PM

Hey,

you can create a calculated column like so

Score CalcColumn = SUM('Table2'[Fee Received]) *'Table2'[Factor]

This returns the following values

The above DAX statement is based on my current knowledge about your datamodel.

Regards

Tom

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2017
02:46 PM

Hi Tom,

Thank you for your reply. But my Fee received is actually already a measure calculated from different table as sum of amount paid. Hence that is not possible . So i need to use SUMX but on a particular classSubclass level.

My Fee received formula is

Fee received = SUM(Remittance[Amount paid]), but even if i apply that i cannot simply take single row value for Factor , it should be sum or aggregate. say if i do this according to your logic:

Sum(Remittance[Amount Paid]) * [Factor], it says below:

"A single value for column 'Factor' in table 'Factor' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I am not sure how to achieve it!

Regards,

Shweta

Announcements

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Featured Topics

Top Solution Authors

User | Count |
---|---|

460 | |

175 | |

117 | |

60 | |

51 |

Top Kudoed Authors

User | Count |
---|---|

451 | |

159 | |

129 | |

74 | |

72 |