DAX Strange Result on Measure

[MarkCBB]

Hi there,

The title of this post needs some explanation.

I have a column in a table called "Sales Ranking" (contains Text Entries, i.e. "CORE", "BLOCKED", "OPEN" etc...)

I have created a Measure to calculate the Sales value of the "CORE" Items, The measure works well. (See below)

(%) CORE CONT = CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE",MAIN[SALES RANKING]="CORE")/CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE")

The above takes the Total Amount of "CORE" "Values" for YTD and divides it by Total amount of "VALUES"

The challenge I have is that I have an existing Visual (Bar Chart) that uses "Sales Ranking" as an Axis (It using a different Value for the chart, not the Measure), and when I click on any of the Items (CORE, BLOCKED, OPEN etc..) the Result of the Measure called "(%) CORE CONT" gives out strange Results. I assume that this is due to the Sales ranking items being used in the Measure?

I have found 2 ways to Get around this, However I doubt that this is the best way to do this.

Work around 1:
I duplicated the "Sales Ranking" Column and used this new column in the Measure.

Work Around 2:

I created a new Table that contains a Unique List of the "Sales Ranking" items and created a relationship between this new table and the "Sales Ranking" Column in the original Table.

As mentioned, I do not think that the above 2 work around's are the best approach and I am sure there is something in the DAX measure that can be done to get around this?

I look forward to any responses regarding the above.

Thanks

 

[Matt Allington]

The denominator of your formula will react to any filter on the MAIN[SALES RANKING] column, eg CORE, BLOCKED, OPEN etc.. The numerator will not react to clicking on the MAIN[SALES RANKING] column because you have explicitly defined this column to be MAIN[SALES RANKING]="CORE" in the numerator. So the top portion is updating correct but the bottom is not. Now I am not 100% sure from you post how you want it to actually work, but one thing you could try is to add ALL(Main) to your filters in the denominator, like this (denominator only)

[COLOR=#333333]CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE",All(Main))[/COLOR]

If that doesn't give you the correct answers, your could try ALLEXCEPT(). It depends what you want. If neither of these work, please post back with more details on the expected answer.

One more tip: You should generally use the DIVIDE function rather than the '/' operator. Divide protects against a zero in the denominator.

 

[MarkCBB]

Hello Again Matt,

Thank you once again for helping me out.

Thank you for the Tip on the DIVIDE Function, I have just tried it out.

With regards to the above challenge I have tried both of your options, but they result in the same value.

(%) CORE CONT = DIVIDE(CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE",MAIN[SALES RANKING]="CORE",ALLEXCEPT(MAIN,MAIN[SALES RANKING])),CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE"),0)

(%) CORE CONT = DIVIDE(CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE",MAIN[SALES RANKING]="CORE",ALL(MAIN)),CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[YEAR]=YEAR(TODAY()),'VALUES'[VALUE TYPE]="VALUE"),0)

The required result would be If I filtered on "OPEN" the "(%) CORE CONT" measure should be zero and If I clicked on "CORE" the result should be 100%.

A better/Simpler Example is the below:

I have a card using the following Measure:

(#) CORE ARTICLES = IF(CALCULATE(DISTINCTCOUNT(MAIN[ARTICLE]),MAIN[SALES RANKING]="CORE")=BLANK(),0,CALCULATE(DISTINCTCOUNT(MAIN[ARTICLE]),MAIN[SALES RANKING]="CORE"))

The above just counts the unique "ARTICLES" that have a "CORE" [SALES RANKING]

I also have a Slicer with the [SALES RANKING], If I click on any item in the slicer the result stays the same. What I would like to do, it if I click on CORE the result is the TOTAL CORE i.e. 15, But If I clicked on "OPEN" the result should be ZERO as "CORE" has now been removed

ALL()

(#) CORE ARTICLES = IF(CALCULATE(DISTINCTCOUNT(MAIN[ARTICLE]),MAIN[SALES RANKING]="CORE",ALL(MAIN))=BLANK(),0,CALCULATE(DISTINCTCOUNT(MAIN[ARTICLE]),MAIN[SALES RANKING]="CORE",ALL(MAIN)))

ALLEXCEPT()

(#) CORE ARTICLES = IF(CALCULATE(DISTINCTCOUNT(MAIN[ARTICLE]),MAIN[SALES RANKING]="CORE",ALLEXCEPT(MAIN,MAIN[SALES RANKING]))=BLANK(),0,CALCULATE(DISTINCTCOUNT(MAIN[ARTICLE]),MAIN[SALES RANKING]="CORE",ALLEXCEPT(MAIN,MAIN[SALES RANKING])))

As always I look forward to your reply.

Thanks

 

[Matt Allington]

The biggest issue I have helping is I don't understand your data model. Eg I don't know which table[column] contains OPEN ETC. any chance you can post a small sample workbook with a pivot (one that doesn't work) and a had created pivot showing what you expect?. I am sure it would be easier then.

 

[MarkCBB]

Hi Matt,

I tried to replicate the Measure in PowerPivot but was not able too, I am using the Power Bi Desktop Designer (Not sure if that makes a difference).

To replicate what I have done, you can use the Excel workbook and load it into PowerBI (it only has the 2 columns). once you have the measure, all you need to do it create a Chart with the sales ranking as an axis and add the measure into a card.
https://dl.dropboxusercontent.com/u/15106990/Book1.xlsx

 

[Matt Allington]

In an earlier post, you mention filtering on "open". I don't see that in your data. You also mention an Amount column and Value Type columns which also are not there. So it is difficult to replicate what you are doing.

 

[MarkCBB]

Hi Matt,

The 2 columns in the file are enough to replicate the result in its simplest form.

I have been Searching the web to see what is going on, and as far as I can understand the function does what it is mean too do, so in order to get around the result I added a duplicate column and use the duplicate column in the axis.

 

[Matt Allington]

I am happy to help, but you will need to share a sample workbook

 

[MarkCBB]

Thank you Matt, I do appreciate your help, I will create the tables during the day.
Thank you once again for helping me out.