Forum Discussion
JonV
Helper II
Helper IIHow to populate missing rows dynamically for calculations?
I'm trying to create a table in Power BI that, at its core, displayed the frequency with which certain items are purchased. This data is being pulled from MS SQL and looks something like this: ...
Hi JonV
You may try below measure:
Measure 2 = VAR a = MAXX ( ALLSELECTED ( Table1 ), Table1[Quantity] ) RETURN IF ( MAX ( 'Table'[Quantity] ) <= a, CALCULATE ( SUM ( Table1[Frequency] ) ) + 0 )Regards,
Cherie
v-cherch-msft
Microsoft Employee
Microsoft EmployeeHi JonV
It seems you may create a measure like below. Attached the sample file. If it is not your case,please share the sample data and expected output.
How to Get Your Question Answered Quickly
Measure = CALCULATE(SUM(Table1[Frequency]))+0
Regards,
Cherie
JonVHelper II
Helper IIThanks Cherie. That solves one problem. However, I still have the problem of needing the quantity list to truncate at the max value of the frequency. Otherwise, when I convert things to a chart, the data for items that only have a few lower values will be scrunched up at one side.
- v-cherch-msft
Hi JonV
I cannot fully understand it. Could you provide the simplified data and expected output for us?
How to Get Your Question Answered Quickly
Regards,
Cherie
- JonVThe issue is that, in order to get it to populate the rows for which there is no quantity, we've joined it to a quantity table. However, as I noted in my OP, the max quantity for some items varies. Sometimes it's 7. Sometimes it's 1,500. So consider what happens when we turn that table into a graph:
In the response you provided previously, you created a quantity table with a max of 30. You can clearly see that when it's graphed, this creates extra points where the graph is flat as there's no actual data there; The real data gets squished up to the left. Now imagine that if the quantity table it's joined to isn't 30, but 1,500. The chart would be unreadable. So my question is how to get it to only display the graph to the max value there actually is data for? I've tried creating the quantity table with GENERATESERIES(1, MAX('Frequency'[Quantity])), in hopes that it would dynamically create the table and that the MAX function would be affected by the slicers, but it doesn't seem to be.
- v-cherch-msft
Hi JonV
You may try below measure:
Measure 2 = VAR a = MAXX ( ALLSELECTED ( Table1 ), Table1[Quantity] ) RETURN IF ( MAX ( 'Table'[Quantity] ) <= a, CALCULATE ( SUM ( Table1[Frequency] ) ) + 0 )Regards,
Cherie