AllExcept not working and Running total %

[Tarek78]

Hi All,

I built a simple data model which you can find as a picture. I have the following tables

1. Dim_Period for the dates

2. Dim_Brand for the brands

3. Dim_ProductGroups > For example Product Group 001 - Filters

4. Dim_ArticleNumber > Each ProductGroup has references/article numbers

5. Dim_PopCode > Each ProductGroup has references/article numbers

6. Fact_FactDataNoFilter > Data table with all information above + revenue + quantity sold

I am want to build a pivot table with the following columns :

1. Product Group > For example 001, 002, 003

2. ArticleNumber > Each article number sold within each product group > Sorted on the highest sales

3. Revenue

4. Quantity Sold

The first issue: I created a measure to highlight the total revenue per group which should be shown for each row within the same product group. However, it doesn't work. This is the DAX I used: =CALCULATE(SUM(Fact_DataNoFilter[Revenue]);ALLEXCEPT(Dim_ProductGroup;Dim_ProductGroup[Grp]))

The second Issue: I want to create a running total % per product group.

Within each product group, I sort the article number selling the most revenue, and then I calculate how it represents in a cumulative way. For example:

Product Group 1 has 3 article number sold: A1 for 10€, X1 for 20€ and B2 for 30€. The total group sold 60€. And I want to see B2 = 30/60= 50%; X1=(30+20)/60= 83.3% and A1 = (10+20+30)/60 = 100%

Not sure if I am clear? I tried those two issues for weeks, for cannot find a solution 😞

Thanks for your help!

[rajendraongole1]

hI @Tarek78  - You want the total revenue for the entire Product Group to appear on each row within that group.

Total Revenue by Product Group =
CALCULATE(
SUM(Fact_DataNoFilter[Revenue]),
ALLSELECTED(Fact_DataNoFilter),
VALUES(Dim_ProductGr[Grp])
)

You can try the cumulative percentage of revenue sorted by the highest-selling article number.

Running Total Revenue =
VAR CurrentArticle = SELECTEDVALUE(Dim_ArticleNum[ArtnrOnly])
VAR CurrentGroup = SELECTEDVALUE(Dim_ProductGr[Grp])
RETURN
CALCULATE(
SUM(Fact_DataNoFilter[Revenue]),
FILTER(
ALL(Dim_ArticleNum),
Dim_ArticleNum[ArtnrOnly] <= CurrentArticle &&
Dim_ProductGr[Grp] = CurrentGroup
)
)

Create a Running Total Percentage Measure

Running Total % =
VAR TotalGroupRevenue =
CALCULATE(
SUM(Fact_DataNoFilter[Revenue]),
ALLSELECTED(Fact_DataNoFilter),
VALUES(Dim_ProductGr[Grp])
)
RETURN
DIVIDE([Running Total Revenue], TotalGroupRevenue, 0)

now sort by revenue . hope this works.  please try.