Totals Column values does not display correct values.

[Anonymous]

Hi there,

New to Power BI but I am having a lot of trouble with this problem. Been researching and looking at other similar questions but I still can't resolve the issue.

Can anyone tell me why the Totals Platform MRR isn't adding up the total of the row?

Figures should read: £1200, £300, £300, £300, £300 respectively from top to bottom in this columns.

Any help greatly appreciated. Thanks in advance.

[v-piga-msft]

Hi @Anonymous ,

What is your Platform MRR? A column or a measure?

If it is a measure, please share your measure formula.

If that is a column, please share your data sample which could reproduce your scenario so that we could help further on it?

Best  Regards,

Cherry

[Anonymous]

Hi @v-piga-msft ,

It is a calculated column.

Client	Client Location	Fee
Client	Client 1	£100.00
Client	Client 2	£100.00
Client	Client 3	£100.00
Client	Client 4	£100.00

[affan]

Hi @Anonymous 

I would suggest to share a sample pbix where you can add some sample data of your table and also create the same visual. This will help resolving your issue faster.

Regards

Affan

[Anonymous]

Hi @affan 

Please follow this link:

https://github.com/PythoNiko/Power_BI/blob/master/TotalsProblem.pbix?raw=true

Hope this is of more use to anyone who can help.

Thanks!

[Anonymous]

Bump.

Can anyone shed any light on this issue please?

[v-piga-msft · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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