Using the Total value of a Column as a separate display

[Anonymous]

Hi All

I have a table that when the blanks are filtered out in the RoSIE column shows the correct % as a total = 72%

Is there a way that I can show the total of 72% in a separate card as a visualization?

Unfortunately the two columns do not have a common denominator at this stage.  I tried to write a DAX formula but just got lost in the process.

Your assistance will be appreciated.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following formula to create measure:

RoSIE Success % = 
VAR tab = 
  SUMMARIZE(
    'Merge VLEvsRoSIEpmnt',
    'Merge VLEvsRoSIEpmnt'[Region Vendor No_],
    "RoSIESum", SUM( 'Merge VLEvsRoSIEpmnt'[RoSIE Processed] ) 
  )
VAR result = 
  CALCULATE(
    DIVIDE(
    SUM('Merge VLEvsRoSIEpmnt'[RoSIE Processed]),
    SUM('Merge VLEvsRoSIEpmnt'[TransTotal])
    ),
    FILTER(
      tab,
      NOT(
        ISBLANK([RoSIESum])
      )
    )
  )
RETURN result

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @Anonymous 

How are youu calculating Rosie %?  Have you tried putting it in a card?

Regards

Phil

[Anonymous]

Hey Phil

I have a table  where each column is calculated separately.  They all add up in the visual and to the eye looks like they balance.

The Total Invoices column includes all types of invoices (RoSIE, EDI, Non Job, etc) and when I filter out the blanks in the RoSIE column it works fine, but to convert it to a DAX formula seems real harder

It would be real easy if I can just use the filtered 72% somehow as a number in a card as a visualization.

[Anonymous]

Hey Phil

So I've dumbed it right down to only a few rows, but the principle will still be the same.

Column A:H is my actual dataset.  Column K:P is a pivot and displays from my actual PowerBI dashboard.

The tricky bit is that some vendors have RoSIE and Other invoices, whilst some vendors only have Other invoices.  Some Vendors may have 100% RoSIE, but that's ok, because I want the RoSIE % anyway. 

I somehow need to exclude vendors who do not have any RoSIE invoices, or who only have Other invoices, from the % calculation.

Maybe I need to add another column or something first before doing the DAX formula.

I have also included two screenshots with displays for the Vendors from my actual dashboard.

Thanks again for your input.  I really appreciate help from the experts.

Cheers

D

[Anonymous]

Hey Phil

Forgot to add:  No vendor will have RoSIE and EDI, so the formula that works for RoSIE will work for EDI as well.  Any vendor can have Other and RoSIE or EDI, but not Other and RoSIE and EDI

[amitchandak]

@Anonymous , How you got RoSIE %, that should work for grand total too

example

divide(sum(Table[RoSIE]), sum(Table[Total Invoice]))

divide(sum(Table[RoSIE]), Count(Invoice[Invoice]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hey Amitchandak

Thanks for replying.  The thing is that when I remove the Blanks form the RoSIE column, the calculation is still correct, but it calculates the RoSIE invoices as a percentage of all invoices, RoSIE related or not. 

I am really only after the 72% as a number in a seperate card in a dashboard.  Just not sure ecaxtly how to approach this one.

[amitchandak]

@Anonymous , Assuming col is a group by /row/column of your table/matrix. Create a formula like

calculate( [RoSIE %], filter(Values(Table[Col1]), not(isnblank(sum(Table[RoSIE]) ))))

[Anonymous]

Thanks Amitchandak - I will give it a go and get back to you shortly

[Anonymous]

Hi Amitchandak

I think we are on the right track, but almost there:

RoSIE Success % = CALCULATE([RoSIE Inv%], FILTER(VALUES('Merge VLEvsRoSIEpmnt'[RoSIE Processed]), NOT(ISBLANK(SUM('Merge VLEvsRoSIEpmnt'[RoSIE Processed])))))

Maybe the DAX formula needs a tweak to get it to 72%

D

[Anonymous]

Hey Amitchandak

So I've dumbed it right down to only a few rows, but the principle will still be the same.

Column A:H is my actual dataset.  Column K:P is a pivot and displays from my actual PowerBI dashboard.

The tricky bit is that some vendors have RoSIE and Other invoices, whilst some vendors only have Other invoices.  Some Vendors may have 100% RoSIE, but that's ok, because I want the RoSIE % anyway. 

I somehow need to exclude vendors who do not have any RoSIE invoices, or who only have Other invoices, from the % calculation.

Maybe I need to add another column or something first before doing the DAX formula.

I have also included two screenshots with displays for the Vendors from my actual dashboard.

Thanks again for your input.  I really appreciate help from the experts.

Cheers

D

[Anonymous]

Forgot to add:  No vendor will have RoSIE and EDI, so the formula that works for RoSIE will work for EDI as well.  Any vendor can have Other and RoSIE or EDI, but not Other and RoSIE and EDI

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula:

RoSIE Success % = 
CALCULATE(
  [RoSIE Inv%], 
  FILTER(
    VALUES( 'Merge VLEvsRoSIEpmnt'[RoSIE Processed] ), 
    NOT( 
      ISBLANK( 'Merge VLEvsRoSIEpmnt'[RoSIE Processed] )
    )
  )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey Winniz

I am attempting to upload an extract (excel spreadsheet) of my dataset.  Don't have permission to upload.

Any suggestions of how I can get it to you?

Cheers

Deon

[v-kkf-msft]

Hi @Anonymous ,

You can try to upload the excel file to OneDrive or SharePoint, and then share the link with me.

Best Regards,
Winniz

[Anonymous]

Hey Winniz

So I've dumbed it right down to only a few rows, but the principle will still be the same.

Column A:H is my actual dataset.  Column K:P is a pivot and displays from my actual PowerBI dashboard.

The tricky bit is that some vendors have RoSIE and Other invoices, whilst some vendors only have Other invoices.  Some Vendors may have 100% RoSIE, but that's ok, because I want the RoSIE % anyway. 

I somehow need to exclude vendors who do not have any RoSIE invoices, or who only have Other invoices, from the % calculation.

Maybe I need to add another column or something first before doing the DAX formula.

I have also included two screenshots with displays for the Vendors from my actual dashboard.

Thanks again for your input.  I really appreciate help from the experts.

Cheers

D

[Anonymous]

Hey Winniz

Forgot to add:  No vendor will have RoSIE and EDI, so the formula that works for RoSIE will work for EDI as well.  Any vendor can have Other and RoSIE or EDI, but not Other and RoSIE and EDI

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following formula to create measure:

RoSIE Success % = 
VAR tab = 
  SUMMARIZE(
    'Merge VLEvsRoSIEpmnt',
    'Merge VLEvsRoSIEpmnt'[Region Vendor No_],
    "RoSIESum", SUM( 'Merge VLEvsRoSIEpmnt'[RoSIE Processed] ) 
  )
VAR result = 
  CALCULATE(
    DIVIDE(
    SUM('Merge VLEvsRoSIEpmnt'[RoSIE Processed]),
    SUM('Merge VLEvsRoSIEpmnt'[TransTotal])
    ),
    FILTER(
      tab,
      NOT(
        ISBLANK([RoSIESum])
      )
    )
  )
RETURN result

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Many thanks for your reply Winniz - just in the middle of something that needs to wrapped up - will apply the formula asap and get back to you.

cheers

D

[Anonymous]

You Da MAN!!!