Forum Discussion
Totals Column values does not display correct values.
Hi Anonymous ，
By my research from your sample pbix, I'm afraid that the subtotal in the matrix shows the correct values based on logic.
As we can see from your data model, the fee has no relationship of Month, the sum of fee Client1 is 100 so that subtotal in matrix is 100.
If you want to show 400 in the row subtoal, you need to create a calcualted column in Performance table and then you could create the desired matrix.
Column = LOOKUPVALUE('Clients'[Fee],Clients[Client Location],'Performance'[Client Name])Here is the output.
Hope this is clear.
Best Regards,
Cherry
Bump.
Can anyone shed any light on this issue please?
Hi Anonymous ，
By my research from your sample pbix, I'm afraid that the subtotal in the matrix shows the correct values based on logic.
As we can see from your data model, the fee has no relationship of Month, the sum of fee Client1 is 100 so that subtotal in matrix is 100.
If you want to show 400 in the row subtoal, you need to create a calcualted column in Performance table and then you could create the desired matrix.
Column = LOOKUPVALUE('Clients'[Fee],Clients[Client Location],'Performance'[Client Name])
Here is the output.
Hope this is clear.
Best Regards,
Cherry
Hi v-piga-msft
Thank you very much for this, it solved the issue - but has raised a further question if I may ask this also?
Let's say I have more than one row of data supplied for any given month. As shown here where we have two entries for April 2019:
This then adds the value of another £100 to my matrix table, showing £200 for Client4, like so:
Is there a way in which we can disregard X amount of entres per month, and only focus on if there is just one entry for the specific month? There should only be one fee regardless of how manytransactions have been made. So if there was 25 entries for April, the fee should only reflect a single row and not all. I hope this makes sense.
The desired output would be for the Total of Client4 to be £400, as per the original question.
I have tried something like this but with no luck, this far:
CALCULATE(FIRSTNONBLANK('storefro_bi dealerships'[platform_fee], FALSE()), FILTER('storefro_bi dealerships', 'storefro_bi dealerships'[dealership_name] =
EARLIER('storefro_bi silverbullet_performance'[dealership_name])) )I was originally going to try build a FIRSTNONBLANK function into the original column expression which you provided as the solution, however we have not addressed the deal_created_at column within this.
I will also attach a link to a new pbix with the data shown above to reflect the update.
https://github.com/PythoNiko/Power_BI/raw/master/TotalsProblem.pbix
Regards,
Niko
Hi
Thank you very much for this, it solved the issue - but has raised a further question if I may ask this also?
Let's say I have more than one row of data supplied for any given month. As shown here where we have two entries for April 2019:
This then adds the value of another £100 to my matrix table, showing £200 for Client4, like so:
Is there a way in which we can disregard X amount of entres per month, and only focus on if there is just one entry for the specific month? There should only be one fee regardless of how manytransactions have been made. So if there was 25 entries for April, the fee should only reflect a single row and not all. I hope this makes sense.
The desired output would be for the Total of Client4 to be £400, as per the original question.
I have tried something like this but with no luck, this far:
CALCULATE(FIRSTNONBLANK('storefro_bi dealerships'[platform_fee], FALSE()), FILTER('storefro_bi dealerships', 'storefro_bi.dealerships'[dealership_name] = EARLIER('storefro_bi silverbullet_performance'[dealership_name])) )I was originally going to try build a FIRSTNONBLANK function into the original column expression which you provided as the solution, however we have not addressed the deal_created_at column within this.
I will also attach a link to a new pbix with the data shown above to reflect the update.
https://github.com/PythoNiko/Power_BI/raw/master/TotalsProblem.pbix
Regards,
Niko