Hi Mei Ming,

This is certainly do-able! Can you attach a .twbx workbook with sample data?  The answer will depend on how your data is set up, particularly the relationship between actual values and category minimums.

Best,

Jennifer

Hi Mei,

Yes, if you could attach a .twbx with the data as you have it that would be great. It will help me (and the community) make more specific suggestions or recommendations both for your original request and if there might be a better way to connect the data. .

Best,

Jennifer

Great. One last question:

Are you comparing the minimum to the count in each sales id (3) or to the category as a whole (6)?

Best,

Jennifer

Hi Mei,

I've attached the workbook with some calculations and sample charts that should get you started.  Have a look and let me know if you have questions. If you haven't worked with LOD (Level of Detail) calculations before (FIXED, INCLUDE, EXCLUDE), see here: Level of Detail Expressions - Tableau . In the calculations below, I've only used the FIXED type of LOD.

Here are a few calculations I created as a basis:

Item Cnt: This is the same as [Number of Records], but more descriptive for calculations.

1

Trx Row: This simply creates an explicit dimension which is the level at which to compare Number of Items to your Minimum (or Target)

[Sale Id] + '_' + [Department] + '_' + [Category]

Trx Row Variance: Finds the difference between the Item Cnt and Minimum for each Trx Row.  I've wrapped it in "ATTR" because we don't want to SUM() across rows, we simply want the number at the Trx Row level of detail.

ATTR(

  { FIXED [Trx Row] : SUM([Item Cnt]) } - [Minimum]

)

Trx Row Variance %: Finds the variance as a % from the Minimum or Target.

ATTR(

  ({ FIXED [Trx Row] : SUM([Item Cnt]) } / [Minimum]) - 1

)

Trx Row Meets Target: Categorizes each TrxRow as True or False based on whether its Item Cnt is below the minimum or not.

{ FIXED [Trx Row] : SUM([Item Cnt]) } >= [Minimum]

Target: This is a copy of [Minimum], but classified as a measure rather than a dimension. For some types of charts it's better for this to be a measure.

With these calculations, you can do standard Actual vs Target, Variance type charts:

Not sure what you had in mind for a heatmap, but here's one option. Bar charts are easier for me to visually process.

And here are some calculations that will help you find number of SalesIDs per Department that have at least one TrxRow not meeting the Minimum:

SalesID Uq Cnt:

COUNTD([Sale Id])

SaleIDs has TrxRow < Minimum: True|False field for each Sale ID whether that Sale ID has at least one Trx Row not meeting the minimum

{ FIXED [Sale Id] : MAX(IF NOT [Trx Row Meets Target] THEN 1 END) }

% SaleID TrxRow < Min: % Sales IDs which have at least one Trx Row not meeting the minimum. In your sample data, this is all of them.

COUNT({ FIXED [Sale Id] : MAX(IF NOT [Trx Row Meets Target] THEN 1 END) }) /

COUNTD([Sale Id])

Oh right, see if this works, I had to switch out datasources (from .hyper to .tdsx) - hopefully everything translates ok.

Best,

Jennifer

Hi Mei,

Without seeing the complexities of your data transformations (how things are aggregated, merged, combined; the difference in levels of detail between the blended data sources), I don't know what to recommend. I would need to see the data and tinker with it for a solution. Can you attach a .twbx workbook that is representative of the complexities of your real data?

Best,

Jennifer