Forum Discussion

Tarek78's avatar
Tarek78
Icon for Helper I rankHelper I
1 year ago

AllExcept not working and Running total %

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!

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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.